• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

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
0
Richard Coffre
Asked:
Richard Coffre
2 Solutions
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now