VBA Loop to Auto Send Emails; How to Format the Body

[kr335606]

Hello,

I am putting together a looping code that that will go down a list of emails in Excel and compose an email automatically, pulling a set of information from another set of cells per user on a monthly basis (the data in these cells will change).

The code I found to utilize will only let me pull in from one column to maintain the loop, but I need to pull in from multiple columns to compose the email.




VB:
Sub Preview()


I = Cells(2, "B").Value ' dynamising startrownumber to user fed value at cell B2


Do ' start the action ,buddy!!!!




Subj = Cells(I, "A").Value
Filepath = Cells(I, "B").Value
EmailTo = Cells(I, "C").Value
CCto = Cells(I, "D").Value
msg = Cells(I, "E").Value 'I THINK THIS IS WHERE AM HAVING TROUBLE. WILL ONLY PULL FROM COLUMN E.


Application.DisplayAlerts = False ' hey macro ,i dont wanna make you take time ,so this command to save time to avoid displays


Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = EmailTo
.CC = CCto
.BCC = ""
.Subject = Subj
.body = msg
.Attachments.Add Filepath
.display


End With
On Error Goto 0
Set OutMail = Nothing
Set OutApp = Nothing


Application.DisplayAlerts = True


I = I + 1


Cells(1, "A").Value = "Outlook sent Time,Dynamic msg preview count =" & I - 3


Loop Until Cells(I, "C").Value = "0"




End Sub


Is there a better way to pull this information in? Basically I have the body of the email typed into the one cell in this column, but this does not allow me to pull in the individual set of data each month in a usable format.

Thank you!!!!
-Kevin

[Excel Fox]

So the information you want to pull is in many columns, across the same row? If yes, start from from Column E, till where? And do you want the information in all these cells to be concatenated?

[kr335606]

Hi there,

Basically, the column E it is reference is pulling in the entire body of the email. So, I essentially have a form letter that says;

"Dear X,

Your data usage for the month is as follows:
Right here is where I would like to be able to reference other data, which is 10 rows of header and 10 rows that would need to be selected by row, for each individual user that is being emailed.

"

It would be great to be able to just have the body be the text, and then be able to reference the individual user data to pull in as some table, but if that isn't possible that is fine. Right now my contingency is to just build a big ="Dear, "&A2&"User"......etc. type formula in the column E's but I know that is a messy solution.

Thank you for your reply.

-Kevin

[Excel Fox]

There is a provision for this already. If you look at the thread http://www.excelfox.com/forum/f18/send-outlook-mail-with-signature-range-from-excel-and-multiple-email-ids-304/, you'll find that the sub-routine in the attached file has a Range argument that needs to be included in the body of the mail. See if you can play around with it. If you aren't able to modify it what you are looking for, post back.

And please follow the rules of Cross-Posting. There is a reason why such rules are in place. If you have posted this query any other place, please post that link too here.

VBA Loop to Auto Send Emails; Pull in More into Body (http://www.ozgrid.com/forum/showthread.php?t=179934)