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

Try this:

ActiveWorkbook.SendMail _
", _
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.



mhng Wrote:
Try this:

ActiveWorkbook.SendMail _
", _
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" wrote in message
...

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 _
", _
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" wrote in message
...

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


--
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

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) ToolsReferences 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 = "
.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 = "



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



"nbaj2k" wrote in message
...

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