Ladies & Gents,

A few people from this newsgroup helped me out with some Excel macro code a
few weeks ago. Your help was terrific! The macro works so well, a child
could use it. I (the child) start it off, and tell it what invoice run
number I've just completed, it picks up the data, formats it, and saves it
to 3 workbooks all from the one number. It's brilliant.

Now I wish to update the macro a little further. I want to open an Outlook
email template, and attach a file to the template. I'm creating 2 reports
for 2 separate customers, so ideally, this is what I want to do;
Create the first workbook
* Open the email template
* Add the previous working day's date to the subject heading & in the
email's text
* Attach the file
* Send the email
Repeat the process for the second customer

Any help with the processes marked with the astericks would be greatly
appreciated. I realise that wanting to place a strange date in the email
may be difficult, so having the message minimise during the process would
not be a problem. Neither would opening the 2 separate templates after the
workbooks have been created. That way I can just attach the files manually
(can I say that here??) & check the messages before sending.

Once more, TIA for you help

Andrew

Andrew,

Here is some code to send mail via Outlook. If you notice, at the end it has
a .Display. This will bring the email up for you to see and send. If you
want it automatically sent, change this to .Send.

Sub SendMail()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim emailDate As Date
Dim sAttachment As String

If Weekday(Date, vbSunday) = vbSunday Then
emailDate = Date - 2
ElseIf Weekday(Date, vbSunday) = vbMonday Then
emailDate = Date - 3
Else
emailDate = Date - 1
End If

sAttachment = "C:\Mytest\Text1.txt"

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

Set oMailItem = oOutlook.CreateItem(0)
With oMailItem
Set oRecipient = ")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
.Subject = "Data for " & Format(emailDate, "dd mmm yyyyy")
.Body = "This is data for " & Format(emailDate, "dd mmm yyyyy")
.Attachments.Add sAttachment
.Display
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andrew" wrote in message
...
Ladies & Gents,

A few people from this newsgroup helped me out with some Excel macro code
a
few weeks ago. Your help was terrific! The macro works so well, a child
could use it. I (the child) start it off, and tell it what invoice run
number I've just completed, it picks up the data, formats it, and saves it
to 3 workbooks all from the one number. It's brilliant.

Now I wish to update the macro a little further. I want to open an
Outlook
email template, and attach a file to the template. I'm creating 2 reports
for 2 separate customers, so ideally, this is what I want to do;
Create the first workbook
* Open the email template
* Add the previous working day's date to the subject heading & in the
email's text
* Attach the file
* Send the email
Repeat the process for the second customer

Any help with the processes marked with the astericks would be greatly
appreciated. I realise that wanting to place a strange date in the email
may be difficult, so having the message minimise during the process would
not be a problem. Neither would opening the 2 separate templates after
the
workbooks have been created. That way I can just attach the files
manually
(can I say that here??) & check the messages before sending.

Once more, TIA for you help

Andrew

Bob,

Thanks for your help. I'll give it a try tomorrow, and see how I go.


Once again
Many Thanks
Andrew

"Bob Phillips" wrote in message
...
Andrew,

Here is some code to send mail via Outlook. If you notice, at the end it
has
a .Display. This will bring the email up for you to see and send. If you
want it automatically sent, change this to .Send.

Sub SendMail()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim emailDate As Date
Dim sAttachment As String

If Weekday(Date, vbSunday) = vbSunday Then
emailDate = Date - 2
ElseIf Weekday(Date, vbSunday) = vbMonday Then
emailDate = Date - 3
Else
emailDate = Date - 1
End If

sAttachment = "C:\Mytest\Text1.txt"

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

Set oMailItem = oOutlook.CreateItem(0)
With oMailItem
Set oRecipient = ")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
.Subject = "Data for " & Format(emailDate, "dd mmm yyyyy")
.Body = "This is data for " & Format(emailDate, "dd mmm yyyyy")
.Attachments.Add sAttachment
.Display
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andrew" wrote in message
...
Ladies & Gents,

A few people from this newsgroup helped me out with some Excel macro
code
a
few weeks ago. Your help was terrific! The macro works so well, a
child
could use it. I (the child) start it off, and tell it what invoice run
number I've just completed, it picks up the data, formats it, and saves
it
to 3 workbooks all from the one number. It's brilliant.

Now I wish to update the macro a little further. I want to open an
Outlook
email template, and attach a file to the template. I'm creating 2
reports
for 2 separate customers, so ideally, this is what I want to do;
Create the first workbook
* Open the email template
* Add the previous working day's date to the subject heading & in the
email's text
* Attach the file
* Send the email
Repeat the process for the second customer

Any help with the processes marked with the astericks would be greatly
appreciated. I realise that wanting to place a strange date in the
email
may be difficult, so having the message minimise during the process
would
not be a problem. Neither would opening the 2 separate templates after
the
workbooks have been created. That way I can just attach the files
manually
(can I say that here??) & check the messages before sending.

Once more, TIA for you help

Andrew