Saturday, July 24, 2010

Excel Macros : Send Email with VBA

The Code I am sharing with you today is capable of sending active sheet of your workbook through outlook mail to email ID specified by you. You will not get any warning messages and the email will be sent using outlook profile available on your machine.

This code saves active sheet to temp file and then email it to the recipient. Once the mail has been sent the temp file is deleted by the code.

You will need to copy this code to regular VBA module of your workbook

Sub EmailActiveSheetWithOutlook()

Dim oApp, oMail As Object, _
tWB, cWB As Workbook, _
FileName, FilePath As String

Application.ScreenUpdating = False

'Set email id here, it may be a range in case you have email id on your worksheet

Mailid = "yogesh@yogeshguptaonline.com"

'Write your email message body here , add more lines using & vbLf _ at the end of each line

Body = "Please find enclosed " & vbLf _
& vbLf _
& "Thanks & Regards"


'Copy Active Sheet and save it to a temporary file

Set cWB = ActiveWorkbook
ActiveSheet.Copy

Set tWB = ActiveWorkbook
FileName = "Temp.xls" 'You can define the name
FilePath = Environ("TEMP")

On Error Resume Next
Kill FilePath & "\" & FileName
On Error GoTo 0
Application.DisplayAlerts = False
tWB.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=56
Application.DisplayAlerts = True

'Sending email through outlook

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = Mailid
.Subject = "Update Message Subject here"
.Body = Body
.Attachments.Add tWB.FullName
.send
End With

'Delete the temporary file and restore screen updating

tWB.ChangeFileAccess Mode:=xlReadOnly
Kill tWB.FullName
tWB.Close SaveChanges:=False
cWB.Activate
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing

End Sub



Download file with VBA code to send active sheet through outlook

16 comments:

  1. APTIMASTERS is basically an online aptitude site which provides the help for the students to make their aptitude skills more enhanced so that they can overcome the entrance examinations for CAT,MAT,UPSC ,Placement Exams etc .

    ReplyDelete
  2. Please help me in counting the total no of common rows in excel vba

    ReplyDelete
  3. Very good Yogesh Sir.. Good Blog.. We also develop advance automation programs in excel at infocorpsolutions.net

    ReplyDelete
  4. Thanks alot!!!
    It really works. Keep on going!

    ReplyDelete
  5. This result of this post has given me much priase at work! thank you!
    What would the code be to copy all sheets into a new Workbook and email?

    ReplyDelete
  6. Can you please tell me how to include multiple email ids for this macro. I want to send my active work sheet to multiple receipients.
    Thanks
    SKS

    ReplyDelete
  7. which part of the code can I delete to use it only to send attachments?

    ReplyDelete
  8. hi yougesh,

    your scripts are very very useful. it saves lots of time. hats off to you.

    ReplyDelete
  9. Hi Yougesh,

    This works like a charm on most PC's, but I have one user that will get an Outlook notification whether to allow or deny sending this email. Do you have any idea on how to get this suppressed or a workaround not to see this on some PC's (at the moment only one).

    Thanks in advance,
    Jesper Andersen

    ReplyDelete
  10. I want to know, how to send mail from Zimbra account. Please let me know.

    ReplyDelete
  11. How would I simply add more than one recipient's email address to send to?

    ReplyDelete
  12. How would I simply add more than one recipient's email address to send to?

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. This is great and efficient. Thank you! Is there a way to modify so that it prompts for the email address?

    Thanks again

    ReplyDelete
  15. Hello Yogesh, You are Awesome !! I'm a Fan of yours !!

    ReplyDelete
  16. What changes are required in this code to send to an email listed in Cell A1 in the excel sheet?

    ReplyDelete