By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,776 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

Sending a report as the email body

AllusiveKitten
P: 43
Hi All

I was just wondering if it was possible to send a report as the email body. I am wanting to send an automatic email to multiple people which will be personalised with their names etc.

Thank you all
AK
Oct 18 '07 #1
Share this Question
Share on Google+
5 Replies


Jim Doherty
Expert 100+
P: 897
Hi All

I was just wondering if it was possible to send a report as the email body. I am wanting to send an automatic email to multiple people which will be personalised with their names etc.

Thank you all
AK
If I am reading you correctly, in order to properly answer this we have to understand the differences in what is termed the body of an email and a standard access report which is usually sent as an RTF attachment rather than in the actual BODY of an email message.

Unless MS add this as standard functionality in Access as an simple.. click "errr put me this actual report into the actual body please" similar to a simple copy paste of an embedded spreadsheet say.... then the short answer is ....No. (Yes we can place a shortcut to the report in the email but it is not the same thing as I perceive you are asking because this links to the original mdb file)

The SNAPSHOT format is an alternative method whereby you can embed (iconic in appearance) a very good image representation of your report and paste it to an email as an attachment. This is viewable using the snapshot viewer that comes with Microsoft Office. It works independant of Access and thus any recipient of the .SNP file does not need Access on their machine but does need to have snapshot viewer installed.

If the specifics of your question is purely centred on the body section of an email then it has to be said that the body section of an email consists of selectable formats 'Plain text' or 'HTML' format or RTF (rich text format).

Typically we usually type directly into the 'body' portion of an email message if we are using 'Outlook' shall we say and then select whether or not we wish to send that email in one of those formats. We then usually add attachments to the email message itself consisting of eternal files ie: xls spreadsheet files .doc word documents and so on.

HTML looks prettier whereas plain text speaks for itself - plain, simple with less overhead and minimal by definition.

Plain text is obvious enough. You might know that you can output a report as an HTML file. So in considering this as a 'possibility' how does the body section represent itself as HTML when it is sent? The text we see as the normal visible text is actually formatted using HTML tags and markers 'behind the scenes' and when it is sent, to put it simply, goes out with those tags and markers. we see none of these tags (neither would we want to given it looks like a jumbled up mess around your actual text message)

Now if we were wanting to make a 'report' look as if it was in the BODY section of an email we would have to get that report representing itself textually speaking with HTML tags.... But an Access Report.. AS IS.. is an Access Report, ie an internal object mechanism of Access. It appears to us as such in an Access preview window and gives us the option to print it or save it as certain formats one of which is.................. HTML.

A haaa! so.....logically if we were to save a report as an external HTML file (either manually or by coded program flow) we could then read that report (which is by now an external html file viewable in a browser) into memory using code, we could take that bunch of tags and other stuff comprising the report content and apply the thing to the .BODY property of an email message using office automation techniques and then maybe we could display the external HTML format in the Outlook body and see it as though we were viewing it normally (or relatively normal if the comparison is set against the Access preview window)

If the question is "can it be achieved in the 'fashion' of a report?"

then the answer is yes (of sorts). I have it working in one of my systems but I consider it workaround set against my perception of the true purpose and meaning of your question.

In order to make it work to apply this type of package referred to in the last two paragraphs would it be for the inexperienced newbie? then my answer would be... No.... you need to have a good grasp of VBA coding and office automation methods.

Advanced? then certainly, a few interactive functions, an awareness of reading file contents into memory, grabbing signature files from outlook (if required) and merging variables to specific areas of file locations concatenating strings. transposing columns to rows... a lot of effort when you consider that with a couple of clicks, a report can be an attachment and anything related to that can be simple text entry in the body.

The question "I was just wondering if was possible" element of your question is like 'candy to a child' to a programmer hence the rather long winded reply at which point if you are not asleep already, then I have to congratulate you LOL!!

Regards

Jim :)
Oct 19 '07 #2

AllusiveKitten
P: 43
Hi Jim

You had better take your hat off, because I am not asleep, however I was dozinig when you made me laugh at the end....

But in the short of it you are saying that I would be better of SQLing the information that would be in each email, and running the email coding in a loop to send individual emails?

I'll be back in a bit with some code....

Thank you so much for your help
AK
Oct 19 '07 #3

Jim Doherty
Expert 100+
P: 897
Hi Jim

You had better take your hat off, because I am not asleep, however I was dozinig when you made me laugh at the end....

But in the short of it you are saying that I would be better of SQLing the information that would be in each email, and running the email coding in a loop to send individual emails?

I'll be back in a bit with some code....

Thank you so much for your help
AK
Basically yes..for my purposes I usually I keep email bodies short sharp and simple if I have to build them and attach everything else either as spreadsheets or whatever else.

As for individual emails you can loop through a table or recipients list and concatenate the recipients as a single line separated by the semi colon and send the same email to all people at once it depends what you want

Jim
Oct 19 '07 #4

AllusiveKitten
P: 43
Hi again,

I have set up coding that steps through the SQL records one at a time as there is multiple changes in the email body.

Private Sub Command0_Click()

Dim Email As String
Dim EmailName As String
Dim SentDate As String
Dim TranNo As String
Dim Office As String
Dim DocName As String
Dim strNote As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set db = CurrentDb()
lsql = "SELECT Tbl_DespactchedDocuments.IssueNo, Tbl_DespactchedDocuments.Date, Tbl_DespactchedDocuments.FullName, TblList_RecipientDetails.EmailAddress, Tbl_DespactchedDocuments.[Project/OfficeName], Tbl_DespactchedDocuments.DocumentTitle FROM Tbl_DespactchedDocuments INNER JOIN TblList_RecipientDetails ON Tbl_DespactchedDocuments.AddressName = TblList_RecipientDetails.AddressName;"
Set lrs = db.openrecordset(lsql)
MsgBox (lrs.RecordCount)
If (lrs.RecordCount) = 0 Then
MsgBox ("No email to be sent!")
Else

Do Until lrs.EOF = True
EmailName = lrs("FullName")
SentDate = lrs("Date")
TranNo = lrs("IssueNo")
Email = lrs("EmailAddress")
Office = lrs("[Project/OfficeName]")
DocName = lrs("DocumentTitle")

strNote = "Dear " & EmailName & vbCrLf
strNote = strNote & vbCrLf & vbCrLf
strNote = strNote & "Was sent " & SentDate & "Send Transmittal back now: No. " & TranNo & vbCrLf & vbCrLf
strNote = strNote & "This is from Me" & vbCrLf
strNote = strNote & "I really hope this works"

Set objOutlook = CreateObject("Outlook.application")

Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail

.To = Email
.Subject = "Test email document"
.Body = strNote
.Send
End With

Set objEmail = Nothing
lrs.MoveNext
Loop
End If

End Sub

Is there a way to stop the pop up box that appears just before each email is sent, asking for confirmation to send the email??

Cheers
AK
Oct 19 '07 #5

Jim Doherty
Expert 100+
P: 897
Hi again,

I have set up coding that steps through the SQL records one at a time as there is multiple changes in the email body.

Private Sub Command0_Click()

Dim Email As String
Dim EmailName As String
Dim SentDate As String
Dim TranNo As String
Dim Office As String
Dim DocName As String
Dim strNote As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set db = CurrentDb()
lsql = "SELECT Tbl_DespactchedDocuments.IssueNo, Tbl_DespactchedDocuments.Date, Tbl_DespactchedDocuments.FullName, TblList_RecipientDetails.EmailAddress, Tbl_DespactchedDocuments.[Project/OfficeName], Tbl_DespactchedDocuments.DocumentTitle FROM Tbl_DespactchedDocuments INNER JOIN TblList_RecipientDetails ON Tbl_DespactchedDocuments.AddressName = TblList_RecipientDetails.AddressName;"
Set lrs = db.openrecordset(lsql)
MsgBox (lrs.RecordCount)
If (lrs.RecordCount) = 0 Then
MsgBox ("No email to be sent!")
Else

Do Until lrs.EOF = True
EmailName = lrs("FullName")
SentDate = lrs("Date")
TranNo = lrs("IssueNo")
Email = lrs("EmailAddress")
Office = lrs("[Project/OfficeName]")
DocName = lrs("DocumentTitle")

strNote = "Dear " & EmailName & vbCrLf
strNote = strNote & vbCrLf & vbCrLf
strNote = strNote & "Was sent " & SentDate & "Send Transmittal back now: No. " & TranNo & vbCrLf & vbCrLf
strNote = strNote & "This is from Me" & vbCrLf
strNote = strNote & "I really hope this works"

Set objOutlook = CreateObject("Outlook.application")

Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail

.To = Email
.Subject = "Test email document"
.Body = strNote
.Send
End With

Set objEmail = Nothing
lrs.MoveNext
Loop
End If

End Sub

Is there a way to stop the pop up box that appears just before each email is sent, asking for confirmation to send the email??

Cheers
AK

It is part of Service pack 2 's security vulnerability update and as you have found out is somewhat annoying when using automation.

You have options to research the direction you wish to take

1) Google - 'redemption in Outlook' << for a full explanation of how to resolve and invoke using this method...loads of references

2) Bye pass Outlook altogether and send using an SMTP server using WINDOWS 2000 CDO Library reference (you could send a copy back to your outlook folder for proof of sending)

3) Use a third party application such as ClickYes PRO in the taskbar to identify when and where the outlook object model is used. This app addresses this annoyance and in effect clicks the button for you.

With regard to the alternative mail sending via an SMTP server

Have a look at my previous thread which outlines some advice I gave and a functional example of how to implement sending via an SMTP server using the reference setting CDO Windows 2000 Library it really is quite easy, merely different and gets round the pop up message.

http://www.thescripts.com/forum/thread704582.html


Regards

Jim :)
Oct 19 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.