Email excel file using outlook

**cpoduri** · 08-30-2012, 05:38 AM

Hi Everyone,

I am new to the world of Macros. I am trying to accomplish a task where I need to email the excel file I am working on to another person using outlook. When I run the macro it should open an outlook compose message with the file as an attachment and a pre designated email addresses (three in total) two in the email TO and one in CC.

Subject of the email should begin with some pre defined text. the mail should not be sent, the macro should stop at opening the compose message with above attributes so that the user can insert custom text in the mail and send it themselves.

Email addresses should be picked up from Cells A 10, A11(in email TO) and A 13(CC)

Hope I managed to articulate my problem well.

Thanks in advance for your help. Much appreciated. Please let me know if any clarification is needed.

Regards,
Ck

**Chippy** · 08-30-2012, 06:06 AM

Based on http://www.rondebruin.nl/mail/folder2/mail1.htm Example 1:

Option Explicit

Sub Mail_workbook_Outlook_1()
'Working in 2000-2010
'This example send the last saved version of the Activeworkbook
    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 = Range("A10").Value & ";" & Range("A11").Value
        .CC = Range("A13").Value
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Make sure you save the workbook in which this code resides before running Mail_workbook_Outlook_1, otherwise the workbook won't be attached to the email.

**cpoduri** · 08-30-2012, 07:10 AM

Brilliant. This is working really well. A small change, can you please tell me how I refer to email addresses in a different sheet if I have to? Thanks a ton.

**Chippy** · 08-30-2012, 10:26 AM

Like this:

Sheets("Sheet1").Range("A13").Value

change Sheet1 to the appropriate sheet name.

**cpoduri** · 08-31-2012, 07:35 PM

Thanks for quick response. Really appreciate it.

Email excel file using outlook

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Email excel file using outlook

Re: Email excel file using outlook

Re: Email excel file using outlook

Re: Email excel file using outlook

Re: Email excel file using outlook

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions