Sending Reports with email body having HTML

[CeVaEs_64]

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.

They really arent defined, I have 3 columns in my main Form (from where they can click each individually to send the e-mail with the reports with the previous Code) and they want me to Create a button to go on top of each column where if you click that button, It would send all the Emails for that Group together (Send An Email with the correspondent Report to the correspondent recipients).

[June7]

Three columns and only one can have data for each record? This is not a normalized data structure.

Since this is marked solved, I presume you have resolved the issue.

[CeVaEs_64]

Three columns and only one can have data for each record? This is not a normalized data structure.

Since this is marked solved, I presume you have resolved the issue.

Well the original question was solved, Yes, this is something else related to the same project, i didnt know if I should open a new thread....

Question is, Instead of me having to click on every client to send the correspondent reports. is there any way that i can create a button on the form where with that one click i can send all the emails with the different reports, at once... something like send all

[June7]

Do you want one email with multiple addressees

or

multiple emails, each to a single addressee?

Either one requires VBA code that loops through a dataset to either:

1. build the multi-address TO string

2. open filtered report, send email, close report, move to next address, repeat

Fairly common topic in forum. https://www.accessforums.net/program...ook-21903.html

[CeVaEs_64]

Do you want one email with multiple addressees

or

multiple emails, each to a single addressee?

Either one requires VBA code that loops through a dataset to either:

1. build the multi-address TO string

2. open filtered report, send email, close report, move to next address, repeat

Fairly common topic in forum. https://www.accessforums.net/program...ook-21903.html

This would be different Emails to different persons with different reports.

lets say i have A B C D Clients, they are all getting their own Email with their own data from their own reports, A Client will get a A report to a A email addresses (with html code) , B Client will get a B report to a B email address (with html code) etc etc


When the body of the email was only a short reminder it was easy, I had one macro that did it individually (on a single click), then i had another macro that will actionate all macros one by one until they were all sent, now since they wanted the body on the email to have red lettering and over 255 characters , i used the code in the first page and did it one by one, and it works wonders. But now they dont want to do it one by one, they want to click a button and send them all together..

The code im using is outputting a report to XLS file, attaching it to an email, sending it and then erasing the outputted file.

[June7]

Then open the appropriate report.

Since you will have 3 buttons, each button will have the same code (with slight variation for the report name) or have the code in a Sub that can be called by each button. The Sub can have arguments and pass parameters to the arguments in the button code that calls the Sub.

[CeVaEs_64]

Then open the appropriate report.

Since you will have 3 buttons, each button will have the same code (with slight variation for the report name) or have the code in a Sub that can be called by each button. The Sub can have arguments and pass parameters to the arguments in the button code that calls the Sub.

Its Me AGAIN!! Sorry

this is where im at... this is the code but is stopping where I highlighted it RED, Im creating a SEND ALL Button

Im sending reports based on a IF function, if there is data on X field then convert a report to .xls, then attach and send email then move on to the next client and so and so (80 clients), at the end im erasing all files created.... What im i missing now?? this is sooo fustrating

Thanks



If (Forms![Main form]!Text171 <> 0) Then
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
DoCmd.OutputTo acOutputReport, "Report A", acFormatXLS, "C:\folder\folder\ReportA.xls", False
With MailOutLook
.BodyFormat = olFormatRichText
.To = "A Emails"
.CC = ""
.BCC = ""
.Subject = "Status"
.HTMLBody = "<HTML>BODY.<body>" & _
"<font color='red'>RED LETTERS</font>" & _
"<body><HTML>" & _
Chr(10) & Chr(13)
.Attachments.Add ("C:\folder\folder\ReportA.xls ")
.DeleteAfterSubmit = False
.send
End With
End If
If (Forms![Main form]!Text172 <> 0) Then
DoCmd.OutputTo acOutputReport, "Report B", acFormatXLS, " C:\folder\folder\ReportB.xls", False
With MailOutLook
.BodyFormat = olFormatRichText
.To = "B EMAILS"
.CC = ""
.BCC = ""
.Subject = "status"
.HTMLBody = "<HTML>Body.<body>" & _
"<font color='red'>Red Letters</font>" & _
"<body><HTML>" & _
Chr(10) & Chr(13)
.Attachments.Add ("C:\folder\folder\ReportB.xls ")
.DeleteAfterSubmit = False
.send
End With
End If
If (Forms![Main form]!Text173 <> 0) Then
DoCmd.OutputTo acOutputReport, "REPORT C", acFormatXLS, " C:\folder\folder\Report C.xls ", False
With MailOutLook
.BodyFormat = olFormatRichText
.To = "C Emails"
.CC = ""
.BCC = ""
.Subject = ""
.HTMLBody = "<HTML> BODY<body>" & _
"<font color='red'>RED LETTERS</font>" & _
"<body><HTML>" & _
Chr(10) & Chr(13)
.Attachments.Add ("C:\folder\folder\Report C.xls ")
.DeleteAfterSubmit = False
.send
End With
End If
Beep
MsgBox "All Reports SENT !", vbOKOnly, "Send All Reports"

Kill "C:\Folder\Folder\*.*"

[June7]

What is the exact error message?

You set the VBA reference for Microsoft Outlook.14.0 Object Library?

Post code within CODE tags and indentation will be preserved and make it easier to read.

[CeVaEs_64]

What is the exact error message?

You set the VBA reference for Microsoft Outlook.14.0 Object Library?

Post code within CODE tags and indentation will be preserved and make it easier to read.

I did set it for the microsoft outlook 14.0,

It says Runtime Error `-1144782582 (bbc4010a) The item ahs been moved or deleted.
once i hit debug it leads me to that line in red...

It converts and sends the first email, it convert the second file but it never gets to send it

Now if i change the .send for .display it doesnt give me any error, but it overwrites the email with the wrong clients, and attaches ALL files together =(

CODE (note this will be done about 80 times over but i only included 2, since the rest would be copy/paste):

Private Sub Command312_Click()
If (Forms![Main form]!Text171 <> 0) Then
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
DoCmd.OutputTo acOutputReport, "Report A", acFormatXLS, "C:\file\File\File\Report A.xls", False
With MailOutLook
.BodyFormat = olFormatRichText
.To = "Email A"
.CC = ""
.BCC = ""
.Subject = "subject"
.HTMLBody = "<HTML>Body<body>" & _
"<font color='red'>Red Letters</font>" & _
"<body><HTML>" & _
Chr(10) & Chr(13)
.Attachments.Add (C:\file\File\File\ReportA.xls")
.DeleteAfterSubmit = False
.Send
End With
End If


If (Forms![Main form]!Text172 <> 0) Then
DoCmd.OutputTo acOutputReport, "Report B", acFormatXLS, "C:\file\File\File\Report B", False
With MailOutLook
.BodyFormat = olFormatRichText
.To = "Email B"
.CC = ""
.BCC = ""
.Subject = "Relatório de Operações Não-Batidas"
.HTMLBody = "<HTML>Body<body>" & _
"<font color='red'>Red Letters</font>" & _
"<body><HTML>" & _
Chr(10) & Chr(13)
.Attachments.Add ("C:\file\File\File\Report B.xls")
.DeleteAfterSubmit = False
.Send
End With
End If


' Here will be the other 78 clients and then it would end like this:


Beep
MsgBox "All Reports SENT !", vbOKOnly, "Send All Reports"

Kill "C:\file\File\File\*.*"


End Sub

[June7]

You want to copy/paste this code 80 times? Has to be a better way, such as opening a recordset of client info and looping through records.

I am not familiar with that error message. That line has never caused me any issues. I can't see any problems with the code syntax.

The code does not open report filtered to desired records so of course it will output ALL.

If you want to provide db for analysis, follow instructions at bottom of my post.

[CeVaEs_64]

You want to copy/paste this code 80 times? Has to be a better way, such as opening a recordset of client info and looping through records.

I am not familiar with that error message. That line has never caused me any issues. I can't see any problems with the code syntax.

The code does not open report filtered to desired records so of course it will output ALL.

If you want to provide db for analysis, follow instructions at bottom of my post.

thanks but This DB is a bit over 4GB LOL, i didnt build it, the guy who did this job before me did it, plus im on a secure PC that wont let me send anything out, Im out of luck i guess... its weird, the first time used the command it sends, so why the second time gives me an error? Also why when i change .send to .Display then it goes through... IDK ill keep on looking and i will keep you posted.

Yeah I already wrote (copy/paste) the same code over and over only replacing report names and emails... sucked but its done

THANKS!

[June7]

I've never had to send multiple emails in one procedure but I've helped with enough code here that I know it should be possible.

Files can be uploaded to a fileshare site such as Box.com and post link. But I guess you would have to take the file somewhere else to do upload.

[CeVaEs_64]

I've never had to send multiple emails in one procedure but I've helped with enough code here that I know it should be possible.

Files can be uploaded to a fileshare site such as Box.com and post link. But I guess you would have to take the file somewhere else to do upload.

He June, I have been reading, and i think that this is happening because im creating the mail item outside of the loop once the first E-mail sends then it fails to exist, i need a new mail object,.... makes sense?? if it does how do i do that?

[CeVaEs_64]

FOUND IT!!

I have to declare this:
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Every time its a different email =)