send outlook email with attachment using excel cell reference

[nikhil0311]

Excel 2007
	A	B	C	D	E
1	To	CC	Subject	Mail Body	Attached File path
2	nikhil@gmail.com	nikhil@gmail.com;sam@gmail.com	Top 50 Report	Hi Nik, Plz find attached Report	C:/Nikhil/Top 50/01.Top 50_APAC_2017-10-23.xls
3	Henry@gmail.com;Alexis@gmail.com	Samuel@gmail.com;Robin@yahoo.com	Top 20 Report	Hi Henry, attached is the Top 10 report for your further action	C:/Nikhil/Top 50/01.Top 10_APAC_2017-10-23.xls
Sheet1

Based on the columns A to E values

1 - I need to send emails with attachment/attachments using outlook.

2 - The name of the recipient varies as well as some of the text in the body of the email. However, each of these fields is a cell in the excel worksheet.

can someone please provide me a VBA code.

Excel version - 2010
Outlook version 2010


Thanks in advance!

 

[daverunt]

Hi,

here's some basic code with no error checking.

Sub Mail_it()


 Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
  
    For Each cell In rng
          
    If cell.Value <> "" Then
       EmailSendTo = cell.Value
       EmailCCTo = cell.Offset(0, 1).Value
       EmailSubject = cell.Offset(0, 2).Value

       EmailBody = cell.Offset(0, 3).Value
       EmailAtt = cell.Offset(0, 4).Value
       
'Send Mail
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(o)
        With OutMail
            .Subject = EmailSubject
            .To = EmailSendTo
            .CC = EmailCCTo
            .Body = EmailBody
            .Attachments.Add EmailAtt
            .display
            '.Send
        End With
        
        Set OutMail = Nothing
        Set OutApp = Nothing
    End If
    Next
End Sub

If you want to base it on whether there's an attachment to send you set 'rng' to the column that is the control column, which would be column E and the offsets would be negative with respect to that column

 

[nikhil0311]

Hey Daverunt. This code is awesome. Thanks a ton for your help. I have 2 follow-up questions;

1) How do I format the bodytext? for instance (Hi Nik, Please find the Top 10 report. if you have any question please reach out to "APAC India" Team)
- Top 10 should be bold
- "APAC India" its an email group address and it should be bold and underlined

2) How do I put the "default siganture in the bottom of the bodytext?


Thanks in advance.

 

[daverunt]

Hi.

you could format that specific string using HTML and use HTMLBody in the email. However that isn't so easy given that the message content is taken from a cell and each message in that column has different content with probably different formatting requirements?
The issue is how many string bits of varying content are there likely to be that need formatting.
You could add the format directly to the cell content first but then there's an issue copying the formatting from the cell to an email body - again I don't think that's straightforward. Probably utilising SendKeys for pasting. I think the RangetoHTML function can copy formatting if it's bodged a bit but there are problems with that too. Also you you may not want the hassle of formatting each cell in Excel up front.

I'll ruminate on it, meanwhile maybe someone else might have some ideas. It might be an idea to add that to a separate post so it gets looked at. You can always link it back to this one.

The default signature. - Have a look at Rons site for that.
https://www.rondebruin.nl/win/s1/outlook/signature.htm

 

[nikhil0311]

thnx for ur help..hope someone have a solution for this request

 

[Macropod]

It's your data structure makes it hard for any formatting to be done. You have the entire (unformatted) email body in a single cell, so a formatting solution is not easy. You would probably need to automate Word as the Outlook Editor and have that use

wildcard

Find/Replace sequences, for example, to find and format the types of content you want to format. Depending on your requirements that could be quite a complex undertaking - one that's beyond what's reasonable to ask for from the free support offered in forums like this.

 

[nikhil0311]

ok. so here is what i want now. instead of taking the mail body just from Column D, ti should take it from D,E,F,G,H,I,J,K. in the respective columns i have applied formatting.

is it possible?

 

[Macropod]

It doesn't really matter whether you've applied the formatting there. What you'd need to do is to define a range for the different columns as you insert their content, then apply the formatting to those ranges using Word as the Outlook Editor; it's still a lot of work.

 

[daverunt]

Hi,

I've sent you a pm with an email address.
Can you send me an example file with about four complete rows in it with the format per cell D to K
If the formatting is the same down a column I can probably apply formatting using HTML as the message is assembled.
But if you are going to format the message up front then using SendKeys might be a simpler solution. I haven't used that but it appears it should be a last resort.
I need to see an example file though.


I don't want to go down the Word Editor route as I've never used it and it would take me a long(er) time.

 

[daverunt]

OK so I have been messing around with Word.
I'm not sure how much use the code will be if you have to format the cells beforehand but the following relies on you formatting a single cell message like you had at the start so that Top 10 is in bold and "APAC India" is underlined.


The cell is copied into a word doc and the table autofit as well as aligned to the top. It is then copied from the doc and pasted into the mail.
Not sure if there's a quicker method. This is my first foray into Word.

You will need to got to Tools ->References in the VBA project and add the reference to the Microsoft Word xx.x Object Library.

Sub Formatted_mail()

Set OutApp = CreateObject("Outlook.Application")
Set wd = CreateObject("Word.Application")

 Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
  
   For Each cell In rng
    
    rRow = cell.Row
          
    If cell.Value <> "" Then
       EmailSendTo = cell.Value
       EmailCCTo = cell.Offset(0, 1).Value
       EmailSubject = cell.Offset(0, 2).Value
       EmailAtt = cell.Offset(0, 4).Value

       Set bodyRng = Cells(rRow, 4)

      bodyRng.Select
      Selection.Copy

   Set doc = wd.Documents.Add
   wd.Visible = True

   wd.Selection.Paste
   doc.Tables(1).AutoFitBehavior _
   wdAutoFitWindow
   
   With doc.Tables(1).Rows(1)
    .Cells.VerticalAlignment = wdAlignVerticalTop
   End With
   
   doc.Content.Copy
   doc.Close SaveChanges:=wdDoNotSaveChanges

        On Error Resume Next
 Set OutMail = OutApp.CreateItem(o)
        With OutMail
            .Subject = EmailSubject
            .To = EmailSendTo
            .CC = EmailCCTo
            .Display
            Set Editor = .GetInspector.WordEditor
            Editor.Content.Paste
            .Attachments.Add EmailAtt
        End With
               
        Set OutMail = Nothing
        Set OutApp = Nothing
    End If
    Next
End Sub