PDA

View Full Version : Solved: Adding comment to excel / outlook script



bacon
09-21-2004, 02:52 AM
Hello,


I have a script that automatically attaches a spreadsheet to an email and sends it to an address list.. Some of the users of the spreadsheet have asked for the facilicty to add a comment to the email before it sends.. is this possible?

the code is:

Sub eMailActiveWorkbook()

Dim OL As Object
Dim EmailItem As Object
Dim Wb As Workbook

ActiveWorkbook.Save

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Wb = ActiveWorkbook
Wb.Save
With EmailItem
.Subject = "I Fowards Spreadsheet has been updated"
.Body = "" & vbCrLf & _
"" & vbCrLf & _
""
.To = "email address list
.Importance = olImportanceHigh 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add Wb.FullName
.Send
End With

Application.ScreenUpdating = True

Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing

End Sub



Many many thanks

Bacon

TonyJollans
09-21-2004, 03:35 AM
Hi Bacon,

When you say Comment, do you mean some text in the body of the e-mail?

If so, a simple solution would be to prompt for the text and then add it, something like ..

Dim EMailComment
EMailComment = InputBox("Please enter your comment for the E-mail")
.Body = EMailComment

Or do you want something with more flexibility?

bacon
09-21-2004, 04:25 AM
Hi Bacon,

When you say Comment, do you mean some text in the body of the e-mail?

If so, a simple solution would be to prompt for the text and then add it, something like ..

Dim EMailComment
EMailComment = InputBox("Please enter your comment for the E-mail")
.Body = EMailComment

Or do you want something with more flexibility?
you are a star.... thank you thank you...