• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

send email incorporating info from excel document

Hello all,

Could you please help me with the creation of the macro.

I have an excel doc that in each row it will contain information including email address about several clients. when I run the macro this is what I would required (This is an example)

1 - it will send a separate email to each person in the column B
2 - in the subject line per email, it would say: REMINDER FOR + the information in column G
3 - it would have a greeting saying: Dear + the information in column A
4 - in the body of the email, it would say something like this but values from the columns will be used too. For example
your color (Column D) will be changed on (Column F). We will go to the address (Column C) using the code (Column G)
5 - As a signature it will be something like: Sincerely, Eddy
6 - and each email will contain the same attachment that it will be located on my computer in a folder called Update, ex: (D:\Update)

Thanks in advance

I attached a sample in order to have a better idea
 

Attachments

Nebu

Excel Ninja
Hi:

This is a commonly asked question in this forum. Just give a search you ca get ready made codes for this.

Thanks
 

chirayu

Well-Known Member
Try this. Choose how you want to attach your file & remove the single quotation mark to turn it from comment to active code segment

Code:
Sub MailWB()

Dim MySubject, MyBody, MyTo, MyWB As String

'Source: http://www.rondebruin.nl/win/s1/outlook/amail1.htm
'Modified by: Chirayu Walawalkar

MyWB = ActiveWorkbook.Name

Range("A2").Select
Do Until IsEmpty(ActiveCell)
   
    MySubject = "Remainder For " & Range("G" & ActiveCell.Row)
   
    MyBody = _
    "Your color " & Range("D" & ActiveCell.Row) & _
    " will be changed on " & Range("F" & ActiveCell.Row) & "." & _
    vbCrLf & vbCrLf & _
    "We will go to the address " & Range("C" & ActiveCell.Row) & _
    " using the code " & Range("G" & ActiveCell.Row) & _
    vbCrLf & vbCrLf & _
    "Sincerely, Eddy"
   
    MyTo = Range("B" & ActiveCell.Row)
       
    Dim OutApp As Object
    Dim OutMail As Object
   
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
   
    On Error Resume Next
    With OutMail
        .To = MyTo
        .CC = ""
        .BCC = ""
        .Subject = MySubject
        .Body = MyBody
       
        'To attach current workbook:
            '.Attachments.Add ActiveWorkbook.FullName
       
        'To attach file stored in drive
            '.Attachments.Add ("C:\test.xlsx")
           
        'To Send the mail directly
            '.Send
           
        'To show the mail & manually click send
            '.Display
           
        .Display
       
    End With
    On Error GoTo 0
   
    Set OutMail = Nothing
    Set OutApp = Nothing
   
    Windows(MyWB).Activate
    ActiveCell.Offset(1, 0).Select
   
Loop

End Sub
 
Hi Chirayu, It worked just like that. I have no words, you are super amazing. Glad life bless you with all that knowledge and also for the good heart of helping people. Thank you so so so so much. you rock :)
 
Hi Chirayu,
I forgot something. In case the Excel doc has several sheets. How do I make this to run as default from the Sheet called RDT.
 

chirayu

Well-Known Member
Code:
Sub MailWB()

Dim MySubject, MyBody, MyTo, MyWB As String

'Source: http://www.rondebruin.nl/win/s1/outlook/amail1.htm
'Modified by: Chirayu Walawalkar

MyWB = ActiveWorkbook.Name

Worksheets("RDT").Select
Range("A2").Select
Do Until IsEmpty(ActiveCell)
 
    MySubject = "Reminder For " & Range("G" & ActiveCell.Row)
 
    MyBody = _
    "Your color " & Range("D" & ActiveCell.Row) & _
    " will be changed on " & Range("F" & ActiveCell.Row) & "." & _
    vbCrLf & vbCrLf & _
    "We will go to the address " & Range("C" & ActiveCell.Row) & _
    " using the code " & Range("G" & ActiveCell.Row) & _
    vbCrLf & vbCrLf & _
    "Sincerely, Eddy"
 
    MyTo = Range("B" & ActiveCell.Row)
     
    Dim OutApp As Object
    Dim OutMail As Object
 
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
 
    On Error Resume Next
    With OutMail
        .To = MyTo
        .CC = ""
        .BCC = ""
        .Subject = MySubject
        .Body = MyBody
     
        'To attach current workbook:
          '.Attachments.Add ActiveWorkbook.FullName
     
        'To attach file stored in drive
          '.Attachments.Add ("C:\test.xlsx")
         
        'To Send the mail directly
          '.Send
         
        'To show the mail & manually click send
          '.Display
         
        .Display
     
    End With
    On Error GoTo 0
 
    Set OutMail = Nothing
    Set OutApp = Nothing
 
    Windows(MyWB).Activate
    ActiveCell.Offset(1, 0).Select
 
Loop

End Sub
 
Thank you Chirayu, you are awesome. you are such an inspiration, you make people world rock. thank you again for your help and support
 
Hi Chirayu, sorry to bother you again. I hope this is the last thing. I forgot that there is a main email address which is located in column B and an alternative email located in column H. I need to be able to send an email to this 2 email address and they will be located in the TO line. I tried to make my own macro but didn't work. It doesn't give me any error but It doesn't put the alternative email

Code:
MyTo = Range("B" & ActiveCell.Row) & ";" & Range("H" & ActiveCell.Row)
 
Never mind, the code is right, It was my mistake. the problem was that in the code I put column H and It was a different column. Sorry about it. it works perfect. Thx for your help
 
Top