Excel Template With Macro For Sending Mails And Attachment Through Outlook

[ramakrishnan]

Dear Experts.

Is there any templates/ macro available to send mails from excel thru outlook where the address , cC address, subject with attachment stored in a target picking the appropriate file from the location specified in the excel sheet as attachment.

A smaple work book is attached for your reference .

This will be a life saver tool to forward emials and save much of the time.


Thanks in Advance expectinga valuable revert.

Regards
R.Ramakrishnan

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg (https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg)
https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg (https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9 (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I)
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3 (https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
ttps://www.youtube.com/watch?v=LP9fz2DCMBE (ttps://www.youtube.com/watch?v=LP9fz2DCMBE)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8 (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8)
ttps://www.youtube.com/watch?v=bFxnXH4-L1A (ttps://www.youtube.com/watch?v=bFxnXH4-L1A)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg)
ttps://www.youtube.com/watch?v=GqzeFYWjTxI (ttps://www.youtube.com/watch?v=GqzeFYWjTxI)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Admin]

Hi RK,

Ron has developed a nice Add-in. You could download the add-in from here (http://www.rondebruin.nl/mail/add-in.htm)

Or if you want templates, see Templates (http://www.rondebruin.nl/mail/templates.htm)

[Excel Fox]

OR you could download this file which does exactly what you asked for....



Option Explicit

'Ensure that you select the Microsoft Outlook X.0 Object Library in the references
'Outlook needs to be loaded, and account logged in

Sub CallMailer()

Dim lngLoop As Long 'Programming ethics 1. Always start your first line after leaving a line space, and 1 indentation level

With ActiveSheet
For lngLoop = 2 To .Cells(Rows.Count, 1).End(xlUp).Row ' Programming ethics 3. Always indent your loops, case statements and with constructors
Call SendMessage(strTo:=.Cells(lngLoop, 1).Value, strCC:=.Cells(lngLoop, 2).Value, strBCC:=.Cells(lngLoop, 7).Value, strMessage:=.Cells(lngLoop, 8).Value, strSubject:=.Cells(lngLoop, 3).Value, strAttachmentPath:=.Cells(lngLoop, 6).Value, rngToCopy:=.Cells(lngLoop, 9))
Next lngLoop
End With 'Programming ethics 2. Always end your last line leaving a line space before ending the sub or function, and having indendation level of 1

End Sub

Sub SendMessage(strTo As String, Optional strCC As String, Optional strBCC As String, Optional strSubject As String, Optional strMessage As String, Optional strAttachmentPath As String, Optional rngToCopy As Range, Optional blnShowEmailBodyWithoutSending As Boolean = False)

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

If Trim(strTo) & Trim(strCC) & Trim(strBCC) = "" Then
MsgBox "Please provide a mailing address!", vbInformation + vbOKOnly, "Missing mail information"
Exit Sub
End If
' Create the Outlook session.
On Error Resume Next
Set objOutlook = GetObject(, "Outlook.Application")
Err.Clear: On Error GoTo -1: On Error GoTo 0
If objOutlook Is Nothing Then
Set objOutlook = CreateObject("Outlook.Application")
End If

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message.
If Trim(strTo) <> "" Then
Set objOutlookRecip = .Recipients.Add(strTo)
objOutlookRecip.Type = olTo
End If

' Add the CC recipient(s) to the message.
If Trim(strCC) <> "" Then
Set objOutlookRecip = .Recipients.Add(strCC)
objOutlookRecip.Type = olCC
End If

' Add the BCC recipient(s) to the message.
If Trim(strBCC) <> "" Then
Set objOutlookRecip = .Recipients.Add(strBCC)
objOutlookRecip.Type = olBCC
End If

' Set the Subject, Body, and Importance of the message.
If strSubject = "" Then
strSubject = "This is an Automation test with Microsoft Outlook"
End If
.Subject = strSubject
If strMessage = "" Then
strMessage = "This is the body of the message." & vbCrLf & vbCrLf
End If
.Importance = olImportanceHigh 'High importance
If Not strMessage = "" Then
.Body = strMessage & vbCrLf & vbCrLf
End If
If Not rngToCopy Is Nothing Then
.HTMLBody = .Body & RangetoHTML(rngToCopy)
End If

' Add attachments to the message.
If Not IsMissing(strAttachmentPath) Then
If Len(Dir(strAttachmentPath)) <> 0 Then
Set objOutlookAttach = .Attachments.Add(strAttachmentPath)
Else
MsgBox "Unable to find the specified attachment. Sending mail anyway."
End If
End If

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

' Should we display the message before sending?
If blnShowEmailBodyWithoutSending Then
.Display
Else
.Save
.Send
End If
End With

Set objOutlook = Nothing
Set objOutlookMsg = Nothing
Set objOutlookAttach = Nothing
Set objOutlookRecip = Nothing

End Sub

'http://msdn.microsoft.com/en-us/library/ff519602(v=office.11).aspx#odc_office_UseExcelObje ctModeltoSendMailPart2_MailingRangeSelectionBody
Function RangetoHTML(rng As Range)

' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

' Copy the range and create a workbook to receive the data.
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

' Publish the sheet to an .htm file.
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

' Read all data from the .htm file into the RangetoHTML subroutine.
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")

' Close TempWB.
TempWB.Close savechanges:=False

' Delete the htm file.
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing

End Function

[ramakrishnan]

Dear Expert,

This forum is really a life saver where the solutions are provide immediately. Thanks a million for the wonderful solution proivdied to me spedning your very valuable time.
This is not only saves my time but also helps me to complete the tasks withing the TAT. I salute you for your help. I need to try this in my office computer where office 2003 is intstalled. Hope this will be compactable with office 2003 also.



Thanks a million

Regards
R.Ramakrishnan

[Excel Fox]

Ramakrishnan, If your office installations have the latest patches and add-ons, it should be able to open the Office 2007+ files. In case you are not able to, I recommend saving the file as 1997-2003 format, and then use it.

[ramakrishnan]

Sir,

I have installed the latest office 2003 sp3 & the file format converters from microsoft but there was 2 errors observed

it opened a window with compile error "can't find project or library" with the highlighted line "


Yellow colour highlighted in the below code

Sub SendMessage(strTo As String, Optional strCC As String, Optional strBCC As String, Optional strSubject As String, Optional strMessage As String, Optional strAttachmentPath As String, Optional blnShowEmailBodyWithoutSending As Boolean = False)

Dim objOutlook As Outlook.Application

If time permists please asssist. We use office 2003 in office.

Thanks and Regards
R.Ramakrishnan

[Excel Fox]

Goto the VBE window, tools>>references>> remove the missing reference library and select Microsoft Outlook 11.0 Object Library

[Excel Fox]

One could use Late Binding (http://word.mvps.org/faqs/interdev/earlyvslatebinding.htm), and work independent of the library being added as reference, but for the sake of performance, I have used Early Binding (http://www.dicks-clicks.com/excel/olBinding.htm), which requires the reference library to be added.

[ramakrishnan]

Sir,

I installed the office Compactability pack also but unfortuntely the XLSM file is not working. Is it possible to convert it inot xla format.

is there any work around so that i can use the sheet i give to you.

REgards
R.Ramakrishnan

[Murali K]

Dear Expert,
Thanks a lot for this macro, really its doing magic. But i need your help to do some changes in this code according to my requirement.
i want body of the message should reflect as below in the email.

"Hi (Name, which is in the first column)

Please find the attached claims report for last week.
if you have any question please contact me@klm.com

Regards
ABC"

(As per your macro,greeting line,body of the message,regards all coming in a single line)

So help me to get it done,if you send updated code my personal id that will be great

my E mail id-muralikusagur@ymail.com

thanks in Advance.....................:)

[Excel Fox]

I haven't tested this. But I think it should work. You can enter the lines and provide line spaces in the cell itself using the ALT+ENTER keys. That way, you will get the exact text as it is in the cell.

[Murali K]

Thanks for your replay,
I have tested 'ALT+Enter' method also and 'wrap-text method, both doesn't worked.................'
can it possible to add body of the message in the code itself.............................
Thanks in advance......

[Excel Fox]

Yes you can. So in place of


strMessage:=.Cells(lngLoop, 8).Value, use


strMessage:="Your message here" & vblf & "Here's the next line"

[burzum]

Thanks for this VB script. It is very close to what I'm looking to do.

My question is: How can I apply this script on a row by row basis?

I would like to be able to send a single or a few emails at a time. For example, if I want to send the email with the attachment and the values are stored in row 6 of the current sheet, is there a way to set the macro to prompt me to enter the row(s) I want it to process for recipient, subject attachment, body, etc?

[Excel Fox]

Can be done. But before suggesting anything, would like to know why do you keep rows for the ones which you don't want to send mail to?

[burzum]

I'm keeping a list of cases that are in progress and what I need to do before my consultation is complete. When a consultation report is finished, I'd like to hit the send mail button to notify the person who submitted the case for my consultation and notify only that person and have my report sent as a file attachment to the email.

[bakerman]

A very basic example on how to automaticaly send email one row at a time.
By putting ok in column I the email is constructed with all of the data in the same row.
At the same time you have a check to which person(s) you already sent an email.

[burzum]

Thank you bakerman, this script is exactly what I'm looking for.

I'm having a problem though in that if I copy a range of cells from another sheet to a blank row, the script triggers regardless of whether or not "OK" is in the targeted cell in the column. How can I modify the script to ONLY trigger when "OK" is typed into the targeted cells?

[bakerman]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 9 Then
If UCase(Target.Value) = "OK" Then
With CreateObject("Outlook.Application").CreateItem(0)
.To = Cells(Target.Row, 8).Value
.Subject = Cells(Target.Row, 4).Value
.Body = "Dear " & Cells(Target.Row, 3).Value & " " & Cells(Target.Row, 2) & "," & vbLf & vbLf & _
"The attachment provided is my final consultation report." & vbLf & vbLf & _
"Sincerely yours" & vbLf & vbLf & _
"Burzum"
.Attachments.Add Cells(Target.Row, 7).Value
.Display '.Send
End With
End If
End If

End Sub

[bandus]

Dear Excel Fox,

I started using this macro and made some modification but nothing significant. However unfortunately I cannot use it for sending for multiple e-mail addresses. I type the emails as the outlook requires (abc@def; ghi@jkl; etc) but it doesn't work. I checked and the addresses are copied into the right field in the right way, but the outlook doesn't recognize them as an e-mail address. This only occurs if I type more than one emails into the excel cell. If I click into the To or CC field in outlook and then click somewhere else or press the tab, the emails get recognized (and underlined) and the message could be sent.

Do you have any idea how could I skip this step which really slows down the distribution of e-mails?

Thanks in advance!

[Excel Fox]

Did you try http://www.excelfox.com/forum/f18/send-outlook-mail-with-signature-range-from-excel-and-multiple-email-ids-304/

[bandus]

No, I did not, and thanks for this really cool macro! However another question came up, but I posted it in this thread you linked me.

Thank you again!

[girapas]

This is an superb macro and I've regularly used it. Sometimes, I need to send more than one, and up to three, attachments. Is it possible to make it running the macro once?
Thanks in advance.

[salleh8]

Firstly thank you for the attached outlook mails file Excel Fox.

However, I have a question, why when sending the sample email you've provided, I received the following message body:

Dear Salleh, Your request for a trip to the islands have been accepted. Congratulations to you and your family. To make payment for this help, please send your money to excelfox@live.in. We accept paypal as well as moneybookers

Instead of,

Dear Salleh,

Your request for a trip to the islands have been accepted. Congratulations to you and your family.

To make payment for this help, please send your money to excelfox@live.in. We accept paypal as well as moneybookers


Please advise. And thank you for your help.

[Excel Fox]

Will have a look at that and get back.

[amitsmarttel]

HELLO ALL,

macro for emails sending work perfectly but now i have other problem, i have one fix table to send in all email on mail body this table is full of color.

can please share macro that perfectly work for this kind of requirement, also attach email body in image format for your review.


thanks in advance.

[jackspero222]

Hi RK,

Ron has developed a nice Add-in. You could download the add-in from here (http://www.rondebruin.nl/mail/add-in.htm)

Or if you want templates, see Templates (http://www.rondebruin.nl/mail/templates.htm)
How I can add 4 cells together