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

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.

Code:

---

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

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?

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

There is a provision for this already. If you look at the thread http://www.excelfox.com/forum/f18/se...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