Send mail using data from the spreadsheet

Hi,

Using the joined file (example), I want to send a mail by clicking on the email address (col C) using data given in cols A and B.

Any ideas ?
Thanks in advance.
20110504-send-mail-via-excel.xlsx
LVL 1
Richard Coffree-commerce Product ManagerAsked:
Who is Participating?
 
royhsiaoCommented:
2) Part 1
select the email address that is related to that row, combine them into 1 cell like the following code:

Sub combine_selected_eamil()
Dim ai As String
    Range("d2").Value = ""
    Selection.SpecialCells(xlCellTypeVisible).Select
    For Each A In Selection
    ai = ai & A.Value & ","
    Next
    ai = Left(ai, Len(ai) - 1)
    Range("d2").Formula = ai
End Sub

Open in new window

0
 
spiderwilk007Commented:
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/

0
 
royhsiaoCommented:
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

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Richard Coffree-commerce Product ManagerAuthor Commented:
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.
0
 
royhsiaoCommented:
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

Open in new window

0
 
royhsiaoCommented:
2) Part 2
put that combine email address to the row you want to send
Use the following code to select the row that you just updated
Sub single_Mail_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 ireply As Integer
    ireply = InputBox(Prompt:="Which row you want to send in email?", Title:="")

    
    A = Worksheets("feuil1").Cells(ireply, 1).Value
    b = Worksheets("feuil1").Cells(ireply, 2).Value
    c = Worksheets("feuil1").Cells(ireply, 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

Open in new window

sample.JPG
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.