Results 1 to 6 of 6

Thread: Send Bulk Personalized E-Mail Through Outlook Using VBA

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

    Lightbulb Send Bulk Personalized E-Mail Through Outlook Using VBA

    Alright here is my file. Everything works, however when I send the email, it comes out as a bulk paragraph instead of a nicely spaced email. Can someone please help me figure out how to add line spacing when sending emails?

    How the current email looks after sending:
    6/5/2013 Good Afternoon Mike Hawk, My name is Cartman and I'm a the head lead at prestege worldwide Inc. I was hoping that you could provide some information about our wonderful program. Our goal is to establish a relationship with Amazon, so
    that we can work together with you. We are a small businesses. We would like to get to know you better. I would love the opportunity to further discuss this venture. Thank you for your time and I hope to hear from you soon. Sincerely, Cartman

    Desired Email:
    6/5/2013


    Good Afternoon Mike Hawk,


    My name is Cartman and I'm a the head lead at prestege worldwide Inc. I was hoping that you could provide some information about our wonderful program.


    Our goal is to establish a relationship with Amazon, so
    that we can work together with you.


    We are a small businesses. We would like to get to know you better.


    I would love the opportunity to further discuss this venture. Thank you for
    your time and I hope to hear from you soon.


    Sincerely,


    Cartman
    Attached Files Attached Files
    Last edited by partho; 06-06-2013 at 01:53 AM.

  2. #2
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Here you go. Find attached file.
    Attached Files Attached Files

  3. #3
    Junior Member
    Join Date
    Jun 2013
    Posts
    9
    Rep Power
    0
    Hey thanks for the updated excel file!
    But now when i send the message it sends the same exact message to everyones.

    So say i want to send:

    "Good Afternoon [personal name],


    My name is Cartman and I'm a the head lead at prestege worldwide Inc. I was hoping that you could provide some information about our wonderful program.


    Our goal is to establish a relationship with [person's company name], so
    that we can work together with you.


    We are a small businesses. We would like to get to know you better.


    I would love the opportunity to further discuss this venture. Thank you for
    your time and I hope to hear from you soon.


    Sincerely,


    [my name]"


    And I send it to "Mike Hawk" who works for "Amazon" and "Eric Grief" who works for "Google"
    They both get this email:

    "Good Afternoon Mike Hawk,


    My name is Cartman and I'm a the head lead at prestege worldwide Inc. I was hoping that you could provide some information about our wonderful program.


    Our goal is to establish a relationship with Amazon, so
    that we can work together with you.


    We are a small businesses. We would like to get to know you better.


    I would love the opportunity to further discuss this venture. Thank you for
    your time and I hope to hear from you soon.


    Sincerely,


    Cartman"

    Meaning that "Eric Grief" gets the email with Mike Hawks name and company on it. Help!
    Last edited by partho; 06-11-2013 at 02:31 AM.

  4. #4
    Junior Member
    Join Date
    Jun 2013
    Posts
    9
    Rep Power
    0
    Let me know if you get confused so I can try to word it better. Basically I want what is exactly in the "H column" in excel to be sent to the person who its is address to in the excel file.


    Use this file to fix:


    Send_Outlook Mail_ExcelFox.xlsm

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    I would have recommended using mail merge for this. Anyway, since this code is available, and you've set up your workbook accordingly, use this.

    Code:
    Option Explicit
    
    
    'Ensure that you select the Microsoft Outlook X.0 Object Library in the references
    'Outlook needs to be loaded, and account logged in
    
    
    Sub CallMailer()
        
        Dim lngLoop As Long 'Programming ethics 1. Always start your first line after leaving a line space, and 1 indentation level
        
        With ActiveSheet
            For lngLoop = 2 To .Cells(Rows.Count, 1).End(xlUp).Row ' Programming ethics 3. Always indent your loops, case statements and with constructors
                Call SendMessage(strTo:=.Cells(lngLoop, 1).Value, strCC:=.Cells(lngLoop, 2).Value, _
                                 strBCC:=.Cells(lngLoop, 7).Value, strMessage:=.Cells(lngLoop, 8).Value, _
                                 strSubject:=.Cells(lngLoop, 3).Value, strAttachmentPath:=.Cells(lngLoop, 6).Value)
            Next lngLoop
        End With 'Programming ethics 2. Always end your last line leaving a line space before ending the sub or function, and having indendation level of 1
        
    End Sub
    
    
     Sub SendMessage(ByVal strTo As String, Optional ByVal strCC As String, Optional ByVal strBCC As String, _
                     Optional ByVal strSubject As String, Optional ByVal strMessage As String, _
                     Optional ByVal strAttachmentPath As String, Optional ByVal rngToCopy As Range, _
                     Optional ByVal blnShowEmailBodyWithoutSending As Boolean = False)
    
    
        Dim objOutlook As Outlook.Application
        Dim objOutlookMsg As Outlook.MailItem
        Dim objOutlookRecip As Outlook.Recipient
        Dim objOutlookAttach As Outlook.Attachment
    
    
        If Trim(strTo) & Trim(strCC) & Trim(strBCC) = "" Then
            MsgBox "Please provide a mailing address!", vbInformation + vbOKOnly, "Missing mail information"
            Exit Sub
        End If
        ' Create the Outlook session.
        On Error Resume Next
        Set objOutlook = GetObject(, "Outlook.Application")
        Err.Clear: On Error GoTo -1: On Error GoTo 0
        If objOutlook Is Nothing Then
            Set objOutlook = CreateObject("Outlook.Application")
        End If
    
    
        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    
    
        With objOutlookMsg
            ' Add the To recipient(s) to the message.
            If Trim(strTo) <> "" Then
                Set objOutlookRecip = .Recipients.Add(strTo)
                objOutlookRecip.Type = olTo
            End If
            
            ' Add the CC recipient(s) to the message.
            If Trim(strCC) <> "" Then
                Set objOutlookRecip = .Recipients.Add(strCC)
                objOutlookRecip.Type = olCC
            End If
    
    
           ' Add the BCC recipient(s) to the message.
           If Trim(strBCC) <> "" Then
                Set objOutlookRecip = .Recipients.Add(strBCC)
                objOutlookRecip.Type = olBCC
            End If
    
    
           ' Set the Subject, Body, and Importance of the message.
           If strSubject = "" Then
                strSubject = "This is an Automation test with Microsoft Outlook"
           End If
           .Subject = strSubject
    
    
           If Not strMessage = "" Then
            .Body = strMessage & vbCrLf & vbCrLf
           End If
           If Not rngToCopy Is Nothing Then
            strMessage = RangetoHTML(rngToCopy)
            .HTMLBody = strMessage
           End If
           .Importance = olImportanceHigh  'High importance
    
    
           ' Add attachments to the message.
           If Not IsMissing(strAttachmentPath) Then
                If Len(Dir(strAttachmentPath)) <> 0 Then
                    Set objOutlookAttach = .Attachments.Add(strAttachmentPath)
                Else
                    MsgBox "Unable to find the specified attachment. Sending mail anyway."
                End If
           End If
    
    
           ' Resolve each Recipient's name.
           For Each objOutlookRecip In .Recipients
               objOutlookRecip.Resolve
           Next
    
    
           ' Should we display the message before sending?
           If blnShowEmailBodyWithoutSending Then
               .Display
           Else
               .Save
               .Send
           End If
        End With
        
        Set objOutlook = Nothing
        Set objOutlookMsg = Nothing
        Set objOutlookAttach = Nothing
        Set objOutlookRecip = Nothing
        
    End Sub
    
    
    Function RangetoHTML(rng As Range)
    
    
    ' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
        Dim fso As Object
        Dim ts As Object
        Dim TempFile As String
        Dim TempWB As Workbook
    
    
        TempFile = Environ$("temp") & Application.PathSeparator & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
     
        ' Copy the range and create a workbook to receive the data.
        rng.Copy
        Set TempWB = Workbooks.Add(1)
        With TempWB.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial xlPasteValues, , False, False
            .Cells(1).PasteSpecial xlPasteFormats, , False, False
            .Cells(1).Select
            Application.CutCopyMode = False
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
     
        ' Publish the sheet to an .htm file.
        With TempWB.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
        End With
     
        ' Read all data from the .htm file into the RangetoHTML subroutine.
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.ReadAll
        ts.Close
        RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                              "align=left x:publishsource=")
     
        ' Close TempWB.
        TempWB.Close savechanges:=False
     
        ' Delete the htm file.
        Kill TempFile
     
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
        
    End Function
    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

  6. #6
    Junior Member
    Join Date
    Jun 2013
    Posts
    9
    Rep Power
    0
    Thanks a million!

Similar Threads

  1. Replies: 12
    Last Post: 12-10-2019, 09:56 PM
  2. Automatically send Birthday E-Mail in Outlook
    By s.ajay88 in forum Outlook Help
    Replies: 2
    Last Post: 03-12-2013, 06:28 PM
  3. Outlook Send Mail With Multiple Recipient and CC
    By noobtron in forum Excel Help
    Replies: 2
    Last Post: 10-31-2012, 07:14 PM
  4. Send Mail Using VBA In Excel And Attach Files
    By macenmin in forum Excel Help
    Replies: 1
    Last Post: 08-03-2012, 01:03 AM
  5. Replies: 11
    Last Post: 11-10-2011, 12:32 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
  •