Excel VBA to send multiple emails based on cell values

[ibanez737]

Hi!

I have a spreadsheet of data essentially containing Names, email addresses, subjects, body text, from field, etc. There are hundreds of rows worth of data and I'm looking for some VBA to send emails based on the data in each row. If there's 100 rows, it would send emails until it reached the last row at 100 and then stop, with all the sending information being located in each column. Could anyone help out with this?

ex:

A2 = Name
B2 = Recipient's email address
C2 = Subject line
D2 = Body of the email
E2 = Sent on behalf of X
F2 = CC
G2 = BCC

Thanks!

 

[Momentman]

Ron de bruin has got it all(outlook mails via excel) See Mail a message to each person in a range

Where would you use the "names" and the "Sent on behalf of"? Are they supposed to be part of the body of the mail

Something like this should get you on track

Sub SendMailToUsers()
    Dim outapp As New Outlook.Application
    Dim outmail As Outlook.MailItem
    Dim wks As Worksheet
    
    Set outapp = CreateObject("outlook.application")
    Set wks = Worksheets("Sheet2")
    
    On Error Resume Next
        
    For I = 2 To wks.Range("A" & Rows.Count).End(xlUp).Row
        Set outmail = outapp.CreateItem(olMailItem)
        With outmail
            .To = wks.Range("B" & I).Value
            .Subject = wks.Range("C" & I).Value
            .Body = wks.Range("D" & I).Value
            .CC = wks.Range("F" & I).Value
            .BCC = wks.Range("G" & I).Value
            .Send
        End With
        On Error GoTo 0
        Set outmail = Nothing
    Next I
            
    Set outapp = Nothing
End Sub

Change sheet2 in the code to the name of the sheet that has the data