Results 1 to 4 of 4

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

  1. #1
    Junior Member
    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    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

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0
    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

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 6
    Last Post: 06-05-2013, 11:33 PM
  2. Export outlook emails to Excel code Error
    By jamilm in forum Outlook Help
    Replies: 2
    Last Post: 02-22-2013, 03:48 PM
  3. VBA editor auto-deletes spaces at the ends of lines
    By LalitPandey87 in forum Excel Help
    Replies: 0
    Last Post: 06-26-2012, 07:53 PM
  4. Speed up Loop VBA
    By PcMax in forum Excel Help
    Replies: 15
    Last Post: 04-09-2012, 04:20 PM
  5. Auto adjust for cells according to value using VBA
    By LalitPandey87 in forum Excel Help
    Replies: 3
    Last Post: 11-08-2011, 08:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •