Email templates & Excel Macros
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
|