Excel Range or Print area into email body (1 Viewer)

MailMan

Registered User.
Local time
Today, 07:49
Joined
Sep 7, 2007
Messages
20
I have an Excel report. It is updated from Access and an email is generated. Now my boss wants the report in the body of the email. I do this manually now but I would like to add it to the VBA so it is added when the report is updated and email created. It can be either an Excel range or print area and could be converted to a picture if needed. Email string body already includes a link to the report and signature. Can anyone point me in the right direction?
 

Acropolis

Registered User.
Local time
Today, 11:49
Joined
Feb 18, 2013
Messages
182
Off the top of my head an array might be a way to go with it, built either from the excel sheet or the dB data.
 

Mark_

Longboard on the internet
Local time
Today, 04:49
Joined
Sep 12, 2017
Messages
2,111
Are you sending the Email as plain text, Rich text, or HTML? If HTML you could build the report as an HTML table inside the body of the Email.
 

boerbende

Ben
Local time
Today, 12:49
Joined
Feb 10, 2013
Messages
339
Here is a small part of code I am using to generate a mail


Set oApp = CreateObject("outlook.application")
Set oMail = oApp.createitem(OLmailitem)

'28-aug
' Build the string for the body if the mail
Email_message = "Dear Customer, <br><br>Please find enclosed "

With oMail

.To = SendTo 'receiver
.CC = SendCC 'carbonCopy
.BCC = reply_address
.Subject = Subject 'SubjectLine

.HTMLBody = Email_message 'email_message & email_message2
' Here you can put whatever your want

.Attachments.Add “Your Filename”
.Display

Do While .ReplyRecipients.Count > 0
.ReplyRecipients.Remove (1) ‘ Remove the sender as reply address
Loop
' .Recipients.Add (reply_address) 'Add company general reply_address
.ReplyRecipients.Add reply_address 'reply_address

End with
 

Mark_

Longboard on the internet
Local time
Today, 04:49
Joined
Sep 12, 2017
Messages
2,111
I'd add the following;
1)
Dim asReport as String

2) I'd put in a function that goes through your excel file and returns a formatted string in HTML

3) I'd set asReport = ReturnFromReportFunction

4) .HTMLBody = Email_Message & asReport

For the function, it would be composed of three parts;
1) Put in the HTML for the table header
2) Loop through records. For each add the body part of the report
3) Add footer that closes report and returns formatted report.

Let us know if this within your skillset or if you need help with any part. Cutting a bigger chunk down into bite size pieces makes some of this far easier to accomplish.
 

Users who are viewing this thread

Top Bottom