Sending Reports with email body having HTML

**CeVaEs_64** · 11-20-2014, 12:41 PM

(sorry if this it the wrong section, Mods please move if it is, Thanks)

Im new to VBA and to this site, First of all I Just want to say Hello to everyone and Thanks in advance.

Second im having a little issue, Everyday I need to send different reports to a bunch of different persons every hour... I had several macros that did it for me, But Then they wanted me to include a e-mail body that was more than 255chars, so i had to go VBA and i have this (for every set of persons and Reports):

Private Sub Command38_Click()
On Error GoTo ErrorHandler
DoCmd.SendObject acReport, "REPORT NAME", "Extension", "EMAILS", "", "SUBJECT", "EMAIL BODY", True, ""
ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Select Case Err
Case 2501
MsgBox "Report Not Sent"
DoCmd.Hourglass False
Resume ExitHandler
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select
End Sub

so everything is good, but NOW they want the body of the email to contain another paragraph totally separated from the first one and they want it highlited and also in Red, How can i add Html to this or do i have to use another method?

I have tried this:

Public Sub TestEmail()

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = "email address here"
''.cc = ""
''.bcc = ""
.Subject = "HTML TEST"
.HTMLBody = "<HTML><body>" & _
"<font color='red'>This is a test</font>" & _
"<body><HTML>" & _
Chr(10) & Chr(13) & Now
''.Attachments.Add (strZip)
.DeleteAfterSubmit = True 'to not save in sent bin
''.Display
.Send
End With
End Sub

But it keeps on giving me errors, about objects not being defined, i have tried everything i read and im just frustrated, Like i said before im a noob, please go easy if this is something stupid, I have been using access/bva for about one week now, but i been reading a lot, looking forward to learn some more and then be a contribuyent instead of a questioner.'

Thanks

**June7** · 11-20-2014, 01:00 PM

Have you set Outlook library reference in VBA editor? Tools > References > Microsoft Outlook 14.0 Object Library

Your code works great.

Suggest you use .Display instead of .Send while testing the HTML formatting.

**CeVaEs_64** · 11-20-2014, 01:31 PM

is not my code, I believe you were the one who wrote it lol, Ok I just tried it and it work, thanks

it sent the e-mail automatically, I want it to open OL and the user to click send phisically with the .display will it do that?

Also How do i run that code from the click of a button?

Sorry for so many questions, Thanks again, im hooked on this =)

EDIT: it did it, it showed before it sent..this is exactly what i wanted but i dont see the option to attach the report.

**June7** · 11-20-2014, 01:44 PM

This process cannot attach an Access object. The report will have to be saved out as a PDF document (look at OutputTo method) then can attach the external PDF file.

Can delete the PDF afterward = Kill can do that, example:

If Dir(strPath & rs!LABNUM & ".pdf") <> "" Then Kill strPath & rs!LABNUM & ".pdf"

**CeVaEs_64** · 11-20-2014, 01:59 PM

Originally Posted by June7

This process cannot attach an Access object. The report will have to be saved out as a PDF document (look at OutputTo method) then can attach the external PDF file.

Can delete the PDF afterward = Kill can do that, example:

If Dir(strPath & rs!LABNUM & ".pdf") <> "" Then Kill strPath & rs!LABNUM & ".pdf"

Great, the first method couldn't add over 255 characters, second method couldn't add colors, 3rd method can't add DB Objects... Lol

I suppose I can export to .xls instead of PDF,

This is getting complicated!! =(

**June7** · 11-20-2014, 02:10 PM

OutputTo has choice of various file types.

More 'user friendly', more code.

What was method that limited what to 255 characters?

**CeVaEs_64** · 11-20-2014, 02:16 PM

At first I was doing it with a macro, and writing the message on the macro field. but then they wanted something longer than 255 chars. i already found out how to link it to a button, now im only missing how to output and then erase.

Many Thanks for your help, ill keep buggin here soon lol

**CeVaEs_64** · 11-20-2014, 03:03 PM

Nothing to read here.... figured it out =))

**CeVaEs_64** · 11-20-2014, 03:24 PM

Originally Posted by June7

OutputTo has choice of various file types.

More 'user friendly', more code.

What was method that limited what to 255 characters?

Ok, so far im here:

Private Sub Command38_Click()

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

DoCmd.OutputTo acOutputReport, "REPORTNAME", acFormatXLS, "Drive:\Folder\Folder\Folder\Folder\Folder\Folder\ Filename.xls", True

With MailOutLook
.BodyFormat = olFormatRichText
.To = "Emails"
.CC = "Emails"
.BCC = ""
.Subject = "Subject"
.HTMLBody = "<HTML>First Paragraph <body>" & _
"<font color='red'>Second and Red paragraph</font>" & _
"<body><HTML>" & _
Chr(10) & Chr(13)
.Attachments.Add ("Drive:\Folder\Folder\Folder\Fodler\Folder\Folder \Report.xls")
.DeleteAfterSubmit = True
.Display

If Dir(Drive:\Folder\Folder\Folder\Folder\Folder\Fold er\ & rs!LABNUM & ".Xls") <> "" Then Kill Drive:\Folder\Folder\Folder\Fodler\Folder\Folder\ & rs!LABNUM & ".Xls"

End With

End Sub

what im i doing wrong in the red line ove there? thats where im stuck now.. Thanks again

**June7** · 11-20-2014, 03:25 PM

Need the literal strings within quote marks. You posted generic example code. Are you actually using a drive letter and real folder names in your code? Could just use the C: root location.

What do you want to use for file name? If the report needs to be filtered before export, need to open first. More example, adapt as needed.

Dim strFile As String
strFile = "C:\TempFile.pdf"
...
DoCmd.OpenReport "REPORTNAME", , , filter criteria here
DoCmd.OutputTo acOutputReport, "REPORTNAME", acFormatXLS, strFile, True
...
.Attachments.Add (strFile)
...
If Dir(strFile) <> "" Then Kill strFile
DoCmd.Close acReport, "REPORTNAME", acSaveNo

Why would you use my example verbatim? It was example from my database. Adapt to your structure.

**CeVaEs_64** · 11-20-2014, 03:35 PM

Originally Posted by June7

Need the literal strings within quote marks. You posted generic example code. Are you actually using a drive letter and real folder names in your code? Could just use the C: root location.

What do you want to use for file name? If the report needs to be filtered before export, need to open first. More example, adapt as needed.

Dim strFile As String
strFile = "C:\TempFile.pdf"
...
DoCmd.OpenReport "REPORTNAME", , , filter criteria here
DoCmd.OutputTo acOutputReport, "REPORTNAME", acFormatXLS, strFile, True
...
.Attachments.Add (strFile)
...
If Dir(strFile) <> "" Then Kill strFile
DoCmd.Close acReport, "REPORTNAME", acSaveNo

Why would you use my example verbatim? It was example from my database. Adapt to your structure.

Sorry this data is really sensitive so i was just being generic, yes it would pull from a network drive not from local drive, I did miss the verbatim tho, part of learning lol
Is there a way where the outputted report wont open? i tried changing the true value to false and it gave me another different error.

EDIT: read your post... i have a bad headache already, i cant have all those reports opening everytime they do that... im done i cant even type ACCESS OVERLOAD

**June7** · 11-20-2014, 03:41 PM

If the report does not need dynamic filter criteria, then don't open it. Could be a very large report without filtering.

If you save to network drive by drive letter reference, every user would need to have that drive mapped by Windows on their computer. Use UNC path or the C: location.

Note that my example uses PDF in filename but you are specifying xls export. These must be in agreement.

What error message?

**CeVaEs_64** · 11-21-2014, 07:08 AM

Originally Posted by June7

If the report does not need dynamic filter criteria, then don't open it. Could be a very large report without filtering.

If you save to network drive by drive letter reference, every user would need to have that drive mapped by Windows on their computer. Use UNC path or the C: location.

Note that my example uses PDF in filename but you are specifying xls export. These must be in agreement.

What error message?

FINALLY got it this morning, THANKS a lot for your help, I will marked as solved, To erase the File I did this instead:

Dim KillFile As String
KillFile = "DRIVE:\Folder\Folder\File.xls"
If Len(Dir$(KillFile)) > 0 Then
SetAttr KillFile, vbNormal
Kill KillFile
End If

**CeVaEs_64** · 11-24-2014, 11:44 AM

Originally Posted by June7

If the report does not need dynamic filter criteria, then don't open it. Could be a very large report without filtering.

If you save to network drive by drive letter reference, every user would need to have that drive mapped by Windows on their computer. Use UNC path or the C: location.

Note that my example uses PDF in filename but you are specifying xls export. These must be in agreement.

What error message?

Hey June, Everything is done and it works perfect, Thanks, But now im beign asked something Else....

I made about 80 different commandXX_Click() with the previous Code, but im being asked to divide the customers into 3 groups and make 3 separated buttons where on each click it would send the email exactly like from the previous code to each group, Lets say i have 99 customers divided in 3 groups of 33 persons, I need 3 separated buttons that on click will send all the emails to the 33 persons on the selected gruop...

I been searching but it getting confusing...

Needless to say, thanks again

**June7** · 11-24-2014, 12:29 PM

How are the groups defined? Is there is a field in customer table that assigns customer to a group?

The OpenReport filter argument can included the group as parameter.

Sending Reports with email body having HTML

Thread Tools

Sending Reports with email body having HTML

Similar Threads

Sending email body as concatenated text based on a cbobox and after update event

Export html to body of email issue

sending a list of open po to each vendor via email in the body

sending data into an email body - outlook

HTML body in Access 2007

Posting Permissions