email with corresponding addresses and names in body AND subject using gmail

Hello everyone,

I did search for this, but couldn't find the exact answer.

I use gmail, and have an excel sheet with hundreds of rows. I need to send each recepient an e-mail using their e-mail address (row D), and their name in the body and subject (row A). The rest of the body of the email will all be the same.

Thanks!

Hi,

Take a look at the following thread.
http://www.excelforum.com/excel-prog...-formulas.html

You'll just need to modify the code with a loop that uses the loop variable to identify the position of an email address in a list and read that value into the .To instruction in the code.

HTH

I'm sorry, I guess Im a little noob here, but I don't even know where to insert that code or what a loop is, or how to change the name in the e-mail that starts with "Dear <name>, .... "

Hello 4thephil,

Can you post the workbook? We can then add the macro into the workbook for you.

Sincerely,
Leith Ross

I can't post the entire workbook as it is private. So, I copied and pasted the first couple of rows and changed the data, and created a new workbook. Hope this works the same? Thanks for your help!

Hello 4thephil,

I have added the macro below to the attached workbook. There is button on Sheet1 to run the macro. The draw back to sending out multiple emails is security. You will have to send each email as the macro cycles through. If I wrote the macro to use Outlook, you would be barraged by annoying dialogs with time delays. Since this is a simple text only email, It would easier and faster to use CDO (Collaboration Data Objects) to send the emails with no annoying messages. The down side of CDO is it more complex and harder to understand. I felt this code would be a good starting point.

'This will Launch the default Email program
 Private Declare Function ShellExecute _
  Lib "Shell32.dll" _
    Alias "ShellExecuteA" _
     (ByVal hWnd As Long, _
      ByVal lpOperation As String, _
      ByVal lpFile As String, _
      ByVal lpParameters As String, _
      ByVal lpDirectory As String, _
      ByVal nShowCmd As Long) As Long

Sub SendEmails()

  Dim Body As String
  Dim Cell As Range
  Dim Email As String
  Dim LastRow As Long
  Dim NameCol As Variant
  Dim RetVal As Long
  Dim Rng As Range
  Dim SendTo As String
  Dim StartRow As Long
  Dim Subject As String
  Dim Wks As Worksheet
    
    NameCol = "A"
    StartRow = 3
    Set Wks = Worksheets("Sheet1")
    
    Subject = "Email test"
    Body = ""
    
      With Wks
        LastRow = .Cells(.Rows.Count, NameCol).End(xlUp).Row
        LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
        Set Rng = .Range(.Cells(StartRow, NameCol), .Cells(LastRow, NameCol))
      End With
      
        For Each Cell In Rng
          SendTo = Cell.Offset(0, 3)   'Email Address in column "D"
          Email = "MailTo:" & SendTo _
                & "?subject=" & Subject _
                & "&body=" & "Dear Professor " & Split(Cell, ",")(0) & "," _
                & vbCrLf & Body
          RetVal = ShellExecute(0&, "open", Email, Chr$(0), Chr$(0), vbHide)
        Next Cell
        
End Sub

Sincerely,
Leith Ross

Wow, thank you so much for taking the time to do this. I loaded that up and clicked the button, and it immediately launched gmail with the e-mails pre-filled out. I will play around with this and tailor it to exactly what I need, but yes, this will work perfectly. Thank you for taking the time to do that! *Very impressed*

HI

its possible change Sudject in everty mail with attachemnet.