Automatically attach Excel spreadsheet to Outlook Email with Macro?

I am at work and made a macro to do some simple automation of some daily reports I have to do. Everyday I have to send this report to one person and CC it to 3 different people. I wanted to include this in the current Macro.

Is there some way in the code to add this so that it automatically opens Microsoft Outlook and makes this an attachment?

Thanks,

~J

Try this:

ActiveWorkbook.SendMail _
Recipients:="abc@email.com", _
Subject:="Subject header" & Format(Date, "dd/mmm/yy")

"nbaj2k" wrote:

>
> I am at work and made a macro to do some simple automation of some daily
> reports I have to do. Everyday I have to send this report to one person
> and CC it to 3 different people. I wanted to include this in the
> current Macro.
>
> Is there some way in the code to add this so that it automatically
> opens Microsoft Outlook and makes this an attachment?
>
> Thanks,
>
> ~J
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=562416
>
>

At risk of hijacking the thread:

Is there anyway to tailor Application.sendmail so that it sends
the current worksheet as the main body of the mail. Excel does this quite readily when sending manually so i assume it is possible. The only code or description I can find online is the Ron de Bruin code, or a variant thereof. This is a nifty little piece of code but i am just sending some summary data concatenated into a letter form so i would rather that it appeared as such right off the the bat.

Hello and goodbye from London.

Originally Posted by mhng

Try this:

ActiveWorkbook.SendMail _
Recipients:="abc@email.com", _
Subject:="Subject header" & Format(Date, "dd/mmm/yy")

"nbaj2k" wrote:

>
> I am at work and made a macro to do some simple automation of some daily
> reports I have to do. Everyday I have to send this report to one person
> and CC it to 3 different people. I wanted to include this in the
> current Macro.
>
> Is there some way in the code to add this so that it automatically
> opens Microsoft Outlook and makes this an attachment?
>
> Thanks,
>
> ~J
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=562416
>
>

Hi Aoife

With OE you can do this
http://www.rondebruin.nl/mail/oebody.htm

But I like CDO
http://www.rondebruin.nl/cdo.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Aoife" <Aoife.2b7ph6_1153345970.9384@excelforum-nospam.com> wrote in message
news:Aoife.2b7ph6_1153345970.9384@excelforum-nospam.com...
>
> At risk of hijacking the thread:
>
> Is there anyway to tailor Application.sendmail so that it sends
> the current worksheet as the main body of the mail. Excel does this
> quite readily when sending manually so i assume it is possible. The
> only code or description I can find online is the Ron de Bruin code, or
> a variant thereof. This is a nifty little piece of code but i am just
> sending some summary data concatenated into a letter form so i would
> rather that it appeared as such right off the the bat.
>
> Hello and goodbye from London.
>
>
>
> mhng Wrote:
>> Try this:
>>
>> ActiveWorkbook.SendMail _
>> Recipients:="abc@email.com", _
>> Subject:="Subject header" & Format(Date, "dd/mmm/yy")
>>
>> "nbaj2k" wrote:
>>
>> >
>> > I am at work and made a macro to do some simple automation of some
>> daily
>> > reports I have to do. Everyday I have to send this report to one
>> person
>> > and CC it to 3 different people. I wanted to include this in the
>> > current Macro.
>> >
>> > Is there some way in the code to add this so that it automatically
>> > opens Microsoft Outlook and makes this an attachment?
>> >
>> > Thanks,
>> >
>> > ~J
>> >
>> >
>> > --
>> > nbaj2k
>> >
>> ------------------------------------------------------------------------
>> > nbaj2k's Profile:
>> http://www.excelforum.com/member.php...o&userid=36480
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=562416
>> >
>> >
>
>
> --
> Aoife
> ------------------------------------------------------------------------
> Aoife's Profile: http://www.excelforum.com/member.php...o&userid=33191
> View this thread: http://www.excelforum.com/showthread...hreadid=562416
>

Hi Aoife

With OE you can do this
http://www.rondebruin.nl/mail/oebody.htm

But I like CDO
http://www.rondebruin.nl/cdo.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Aoife" <Aoife.2b7ph6_1153345970.9384@excelforum-nospam.com> wrote in message
news:Aoife.2b7ph6_1153345970.9384@excelforum-nospam.com...
>
> At risk of hijacking the thread:
>
> Is there anyway to tailor Application.sendmail so that it sends
> the current worksheet as the main body of the mail. Excel does this
> quite readily when sending manually so i assume it is possible. The
> only code or description I can find online is the Ron de Bruin code, or
> a variant thereof. This is a nifty little piece of code but i am just
> sending some summary data concatenated into a letter form so i would
> rather that it appeared as such right off the the bat.
>
> Hello and goodbye from London.
>
>
>
> mhng Wrote:
>> Try this:
>>
>> ActiveWorkbook.SendMail _
>> Recipients:="abc@email.com", _
>> Subject:="Subject header" & Format(Date, "dd/mmm/yy")
>>
>> "nbaj2k" wrote:
>>
>> >
>> > I am at work and made a macro to do some simple automation of some
>> daily
>> > reports I have to do. Everyday I have to send this report to one
>> person
>> > and CC it to 3 different people. I wanted to include this in the
>> > current Macro.
>> >
>> > Is there some way in the code to add this so that it automatically
>> > opens Microsoft Outlook and makes this an attachment?
>> >
>> > Thanks,
>> >
>> > ~J
>> >
>> >
>> > --
>> > nbaj2k
>> >
>> ------------------------------------------------------------------------
>> > nbaj2k's Profile:
>> http://www.excelforum.com/member.php...o&userid=36480
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=562416
>> >
>> >
>
>
> --
> Aoife
> ------------------------------------------------------------------------
> Aoife's Profile: http://www.excelforum.com/member.php...o&userid=33191
> View this thread: http://www.excelforum.com/showthread...hreadid=562416
>

Hi Aoife

With OE you can do this
http://www.rondebruin.nl/mail/oebody.htm

But I like CDO
http://www.rondebruin.nl/cdo.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Aoife" <Aoife.2b7ph6_1153345970.9384@excelforum-nospam.com> wrote in message
news:Aoife.2b7ph6_1153345970.9384@excelforum-nospam.com...
>
> At risk of hijacking the thread:
>
> Is there anyway to tailor Application.sendmail so that it sends
> the current worksheet as the main body of the mail. Excel does this
> quite readily when sending manually so i assume it is possible. The
> only code or description I can find online is the Ron de Bruin code, or
> a variant thereof. This is a nifty little piece of code but i am just
> sending some summary data concatenated into a letter form so i would
> rather that it appeared as such right off the the bat.
>
> Hello and goodbye from London.
>
>
>
> mhng Wrote:
>> Try this:
>>
>> ActiveWorkbook.SendMail _
>> Recipients:="abc@email.com", _
>> Subject:="Subject header" & Format(Date, "dd/mmm/yy")
>>
>> "nbaj2k" wrote:
>>
>> >
>> > I am at work and made a macro to do some simple automation of some
>> daily
>> > reports I have to do. Everyday I have to send this report to one
>> person
>> > and CC it to 3 different people. I wanted to include this in the
>> > current Macro.
>> >
>> > Is there some way in the code to add this so that it automatically
>> > opens Microsoft Outlook and makes this an attachment?
>> >
>> > Thanks,
>> >
>> > ~J
>> >
>> >
>> > --
>> > nbaj2k
>> >
>> ------------------------------------------------------------------------
>> > nbaj2k's Profile:
>> http://www.excelforum.com/member.php...o&userid=36480
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=562416
>> >
>> >
>
>
> --
> Aoife
> ------------------------------------------------------------------------
> Aoife's Profile: http://www.excelforum.com/member.php...o&userid=33191
> View this thread: http://www.excelforum.com/showthread...hreadid=562416
>

Hi Aoife

With OE you can do this
http://www.rondebruin.nl/mail/oebody.htm

But I like CDO
http://www.rondebruin.nl/cdo.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Aoife" <Aoife.2b7ph6_1153345970.9384@excelforum-nospam.com> wrote in message
news:Aoife.2b7ph6_1153345970.9384@excelforum-nospam.com...
>
> At risk of hijacking the thread:
>
> Is there anyway to tailor Application.sendmail so that it sends
> the current worksheet as the main body of the mail. Excel does this
> quite readily when sending manually so i assume it is possible. The
> only code or description I can find online is the Ron de Bruin code, or
> a variant thereof. This is a nifty little piece of code but i am just
> sending some summary data concatenated into a letter form so i would
> rather that it appeared as such right off the the bat.
>
> Hello and goodbye from London.
>
>
>
> mhng Wrote:
>> Try this:
>>
>> ActiveWorkbook.SendMail _
>> Recipients:="abc@email.com", _
>> Subject:="Subject header" & Format(Date, "dd/mmm/yy")
>>
>> "nbaj2k" wrote:
>>
>> >
>> > I am at work and made a macro to do some simple automation of some
>> daily
>> > reports I have to do. Everyday I have to send this report to one
>> person
>> > and CC it to 3 different people. I wanted to include this in the
>> > current Macro.
>> >
>> > Is there some way in the code to add this so that it automatically
>> > opens Microsoft Outlook and makes this an attachment?
>> >
>> > Thanks,
>> >
>> > ~J
>> >
>> >
>> > --
>> > nbaj2k
>> >
>> ------------------------------------------------------------------------
>> > nbaj2k's Profile:
>> http://www.excelforum.com/member.php...o&userid=36480
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=562416
>> >
>> >
>
>
> --
> Aoife
> ------------------------------------------------------------------------
> Aoife's Profile: http://www.excelforum.com/member.php...o&userid=33191
> View this thread: http://www.excelforum.com/showthread...hreadid=562416
>

Hi nbaj2k

You must use the Outlook object model if you want to use CC.
Not possible with SendMail

See my site for example code (click also on the Tip link on every example page)
http://www.rondebruin.nl/sendmail.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"nbaj2k" <nbaj2k.2b546q_1153224606.7121@excelforum-nospam.com> wrote in message
news:nbaj2k.2b546q_1153224606.7121@excelforum-nospam.com...
>
> I am at work and made a macro to do some simple automation of some daily
> reports I have to do. Everyday I have to send this report to one person
> and CC it to 3 different people. I wanted to include this in the
> current Macro.
>
> Is there some way in the code to add this so that it automatically
> opens Microsoft Outlook and makes this an attachment?
>
> Thanks,
>
> ~J
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=562416
>

Hi nbaj2k

You must use the Outlook object model if you want to use CC.
Not possible with SendMail

See my site for example code (click also on the Tip link on every example page)
http://www.rondebruin.nl/sendmail.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"nbaj2k" <nbaj2k.2b546q_1153224606.7121@excelforum-nospam.com> wrote in message
news:nbaj2k.2b546q_1153224606.7121@excelforum-nospam.com...
>
> I am at work and made a macro to do some simple automation of some daily
> reports I have to do. Everyday I have to send this report to one person
> and CC it to 3 different people. I wanted to include this in the
> current Macro.
>
> Is there some way in the code to add this so that it automatically
> opens Microsoft Outlook and makes this an attachment?
>
> Thanks,
>
> ~J
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=562416
>

Hi nbaj2k

You must use the Outlook object model if you want to use CC.
Not possible with SendMail

See my site for example code (click also on the Tip link on every example page)
http://www.rondebruin.nl/sendmail.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"nbaj2k" <nbaj2k.2b546q_1153224606.7121@excelforum-nospam.com> wrote in message
news:nbaj2k.2b546q_1153224606.7121@excelforum-nospam.com...
>
> I am at work and made a macro to do some simple automation of some daily
> reports I have to do. Everyday I have to send this report to one person
> and CC it to 3 different people. I wanted to include this in the
> current Macro.
>
> Is there some way in the code to add this so that it automatically
> opens Microsoft Outlook and makes this an attachment?
>
> Thanks,
>
> ~J
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=562416
>

Hi nbaj2k

You must use the Outlook object model if you want to use CC.
Not possible with SendMail

See my site for example code (click also on the Tip link on every example page)
http://www.rondebruin.nl/sendmail.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"nbaj2k" <nbaj2k.2b546q_1153224606.7121@excelforum-nospam.com> wrote in message
news:nbaj2k.2b546q_1153224606.7121@excelforum-nospam.com...
>
> I am at work and made a macro to do some simple automation of some daily
> reports I have to do. Everyday I have to send this report to one person
> and CC it to 3 different people. I wanted to include this in the
> current Macro.
>
> Is there some way in the code to add this so that it automatically
> opens Microsoft Outlook and makes this an attachment?
>
> Thanks,
>
> ~J
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=562416
>

I can't get to that site from here at work.

I have looked at code around and pieced some things together this is what I have so far, I still have no idea how to attach the current file I'm in automatically.

Any other ideas?

The bolded line is the one I somehow want to change to make it automatically pick the sheet I'm in to attach so I don't have to pick it.

Thanks,

~J

Sub Email()
Dim myattachment
Dim olNs As Object
Dim olMailItem
Dim olMail As Object
Dim olApp As Object
Dim ATTACH1 As String


ATTACH1 = Application.GetOpenFilename("Text Files (*.*), *.*")
Set olApp = CreateObject("Outlook.Application")
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon
Set olMail = olApp.CreateItem(olMailItem)
olMail.Display

olMail.To = "(ADDRESS)"
olMail.CC = "(ADDRESS)"
'olMail.BCC =
olMail.Subject = "Daily Report for: " & Format(Date - 1, "d-mmm-yy")
olMail.Body = vbCr & vbCr & "(TEXT HERE)" & Format(Date, "d-mmm-yy") & "
Set myattachment = olMail.Attachments
myattachment.Add ATTACH1
Stop
'olMail.Send
olNs.Logoff
Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing
End Sub

Hi nbaj2k

>I can't get to that site from here at work.

Why they block NL sites ?
Can you ask that for me

Try this

This example send the last saved version of the Activeworkbook.
You must add a reference to the Microsoft outlook Library.

1) Go to the VBA editor, Alt -F11
2) Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number


Sub Mail_workbook_Outlook()
'This example send the last saved version of the Activeworkbook
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "ron@debruin.nl"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

You can use this for more addresses
..To = "ron@debruin.nl;jelle@debruin.nl"



--
Regards Ron de Bruin
http://www.rondebruin.nl



"nbaj2k" <nbaj2k.2bv95s_1154444106.9675@excelforum-nospam.com> wrote in message
news:nbaj2k.2bv95s_1154444106.9675@excelforum-nospam.com...
>
> I can't get to that site from here at work.
>
> I have looked at code around and pieced some things together this is
> what I have so far, I still have no idea how to attach the current file
> I'm in automatically.
>
> Any other ideas?
>
> The bolded line is the one I somehow want to change to make it
> automatically pick the sheet I'm in to attach so I don't have to pick
> it.
>
> Thanks,
>
> ~J
>
> Sub Email()
> Dim myattachment
> Dim olNs As Object
> Dim olMailItem
> Dim olMail As Object
> Dim olApp As Object
> Dim ATTACH1 As String
>
>
> ATTACH1 = APPLICATION.GETOPENFILENAME(\"TEXT FILES (*.*), *.*\")
> Set olApp = CreateObject("Outlook.Application")
> Set olNs = olApp.GetNamespace("MAPI")
> olNs.Logon
> Set olMail = olApp.CreateItem(olMailItem)
> olMail.Display
>
> olMail.To = "(ADDRESS)"
> olMail.CC = "(ADDRESS)"
> 'olMail.BCC =
> olMail.Subject = "Daily Report for: " & Format(Date - 1, "d-mmm-yy")
> olMail.Body = vbCr & vbCr & "(TEXT HERE)" & Format(Date, "d-mmm-yy") &
> "
> Set myattachment = olMail.Attachments
> myattachment.Add ATTACH1
> Stop
> 'olMail.Send
> olNs.Logoff
> Set olNs = Nothing
> Set olMail = Nothing
> Set olApp = Nothing
> End Sub
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=562416
>