VBA code to send emails using email addresses from 2 columns in Excel

[dougdrex]

Using VBA code in Excel 2010 file.

I have a data set with about 300 records. I need to send emails (using Outlook) to email addresses from two columns in the Excel file. The email will have a preset Subject and text that goes in the body of the email. In addition to the text in the body, I also need to copy the header row of the spreadsheet along with the row of data that the email address is found in (I hope that makes sense).

Since there will be about 300 emails, it would also be nice if the VBA code could also execute the Send command for all the email messages.

This might be more simple than I think, but I'm completely unfamiliar with VBA using Outlook objects and commands. Any help is greatly appreciated!


Thanks!
Doug

 

[Anticor]

This might help some (at the very least it's a kicking off point!).

Here's what I use to email out of my spreadsheet:

 Dim OutlookApp As Object
        Dim Mess As Object, Recip As String
        Recip = ws.Range("A2").Value & "@company.com" 'I'm storing user name in column A2 so this makes my recipient user@company.com
        Set OutlookApp = CreateObject("Outlook.Application")
        Set Mess = OutlookApp.CreateItem(olMailItem)
        With Mess
            .Subject = "Your subject message here" 'Premade subject line
            .Body = "Your body here if you want to add information from a cell in use the following " & " " & ws.Range("C1").Value & " " & " continue your premade email body as needed.  Thank you." 'Premade body with the option of pulling information out of the worksheet and adding it to the body
            .Recipients.Add Recip
            .Send
        Call SaveAs
        End With

 

[dougdrex]

This might help some (at the very least it's a kicking off point!).

Here's what I use to email out of my spreadsheet:

 Dim OutlookApp As Object
        Dim Mess As Object, Recip As String
        Recip = ws.Range("A2").Value & "@company.com" 'I'm storing user name in column A2 so this makes my recipient user@company.com
        Set OutlookApp = CreateObject("Outlook.Application")
        Set Mess = OutlookApp.CreateItem(olMailItem)
        With Mess
            .Subject = "Your subject message here" 'Premade subject line
            .Body = "Your body here if you want to add information from a cell in use the following " & " " & ws.Range("C1").Value & " " & " continue your premade email body as needed.  Thank you." 'Premade body with the option of pulling information out of the worksheet and adding it to the body
            .Recipients.Add Recip
            .Send
        Call SaveAs
        End With

I'm trying out this code to see if it will work for what I'm trying to accomplish. Do you know how I could have the code go through each line and do one of two things. Either create a new email for each line or create a single email and insert all the email addresses while attaching the entire file. Both solutions would work as far as I know.

 

[Anticor]

I'm trying out this code to see if it will work for what I'm trying to accomplish. Do you know how I could have the code go through each line and do one of two things. Either create a new email for each line or create a single email and insert all the email addresses while attaching the entire file. Both solutions would work as far as I know.

You could probably loop through it (something I learned myself just yesterday):

'Define my first row where I expect to find data so let's say you've got a header, the first record would be row 2
i = 2


'Do loop, where the condition is as long as a particular column is not blank keep going. it's cells(row, column)
'In this example the Do is going to start in A1 and then move to A2, A3, etc. until it hits a row where A is blank 


Do While (Cells(i, 1) <> "")


  'Here it found A1 with a value that wasn't blank so it sends an email
    Dim OutlookApp As Object
        Dim Mess As Object, Recip As String
        Recip = ws.Range("A2").Value & "@company.com" 'I'm storing user name in column A2 so this makes my recipient user@company.com
        Set OutlookApp = CreateObject("Outlook.Application")
        Set Mess = OutlookApp.CreateItem(olMailItem)
        With Mess
            .Subject = "Your subject message here" 'Premade subject line
            .Body = "Your body here if you want to add information from a cell in use the following " & " " & ws.Range("C1").Value & " " & " continue your premade email body as needed.  Thank you." 'Premade body with the option of pulling information out of the worksheet and adding it to the body
            .Recipients.Add Recip
            .Send
        Call SaveAs
        End With
    
	'After that email is sent we increment i and go back to the top so we'll look at A2, etc.
    i = i + 1
Loop

Let me know if that makes any sense at all.

 

[dougdrex]

That makes great sense. Thanks for your help, I REALLY appreciate it!