Send mail using data from the spreadsheet

This is the best way to do it. This example is for Office 2010, let me know if you need the link for any other versions.

http://www.addictivetips.com/microsoft-office/word-2010-mail-merge/

Another way to do it is to use macro like the following:

Sub Mail_small_Text_Outlook()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim a As String
    Dim b As String
    Dim c As String
       
    a = Worksheets("feuil1").Cells(1, 1).Value
    b = Worksheets("feuil1").Cells(1, 2).Value
    c = Worksheets("feuil1").Cells(1, 3).Value
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = a
    
    'On Error Resume Next
    With OutMail
        .to = c
        .CC = ""
        .BCC = ""
        .Subject = b
        .Body = strbody
        .display
        'You can add a file like this
        '.Attachments.Add ("C:\app.txt")
        '.Send   <-- remove the ' to send the email
    End With
    'On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Select allOpen in new window

Hi royhsiao,

Your solution fits better my problem than the first one.

I see 2 ways to really solve my problem :
1. how to parse all the lines of an Excel file and not only the 1st one.
2. how to  click on the email address of a row and send the email related to this line.

thanks in advance.

1)
Use a for loop will parse all the lines of an excel file and not only the 1st one like the following

Sub Mail_all_Text_Outlook()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim a As String
    Dim b As String
    Dim c As String
    Dim i As Integer
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.ActiveSheet
    Dim lastRow As Integer
    lastRow = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1
   
    For i = 2 To lastRow
    a = Worksheets("feuil1").Cells(i, 1).Value
    b = Worksheets("feuil1").Cells(i, 2).Value
    c = Worksheets("feuil1").Cells(i, 3).Value
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = a
    
    'On Error Resume Next
    With OutMail
        .to = c
        .CC = ""
        .BCC = ""
        .Subject = b
        .Body = strbody
        .display
        'You can add a file like this
        '.Attachments.Add ("C:\app.txt")
        '.Send   <-- remove the ' to send the email
    End With
    'On Error GoTo 0
    Next i
    
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Select allOpen in new window

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.