Save PDF and mail macro? (Excel 2003)

[sirrusja]

Dear All,

I want to combine saving Excel worksheet into PDF file and mail this file to some recipients. I've done this in Excel 2007 with no problems, but the task is to make it possible in Excel 2003 (I am kind of novice in script writing).
I've found separate scripts one for saving PDF file (using CutePDF) and another one to send worksheet without attachments. Here they are:
-------------------------------------------------------------------------

Sub Button1_Click()

' This line of code calls the Adobe PDF printer and runs the conversion.
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True

' This set of code tells the macro to pause for 2 seconds.
' This will allow for the PDF printer to run through its process and prompt you for a filename.
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

' This line of code specifies your directory as well as the cell or range which you want the filename to come from.
Filename = "C:\Temp\Temp\" & Format(Now, "Report - yyyy-mm-dd hh-mm-ss") & ".pdf"

' This line of code sends the filename characters and the ENTER key to the active application (i.e. the prompt window). The "False" statement allows the macro to continue running without waiting for the keys to be processed.

SendKeys Filename & "{ENTER}", False

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 - 0
ElseIf Weekday(Date, vbSunday) = vbMonday Then
emailDate = Date - 0
Else
emailDate = Date - 0
End If

sAttachment = "C:\Temp\Temp\" & Format(Now, "Report - yyyy-mm-dd hh-mm-ss") & ".pdf"

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

Set oMailItem = oOutlook.CreateItem(0)
With oMailItem
Set oRecipient = .Recipients.Add("test@test.com")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
.Subject = "Report for " & Format(emailDate, "dd mmm yyyyy")
.Body = "Please find attached file for " & Format(emailDate, "dd mmm yyyyy")
.Attachments.Add sAttachment
.Display
End With

End Sub

-------------------------------------------------------------------------
So the PDF file is created succesfully, but scrips stops everytime on the following line: Set oOutlook = CreateObject("Outlook.Application")

Any ideas?

 

[Rasmus]

Have you added the Outlook libary in the Visual Basic Editor --> Tools --> Refrences ?

 

[sirrusja]

Have you added the Outlook libary in the Visual Basic Editor --> Tools --> Refrences ?

Thanks a lot Rasmus! I added Outlook library and my script went a liitle bit further now.
This time I stuck at the very end on the line: .Attachments.Add sAttachment

I guess this line shoud work with: sAttachment = "C:\Temp\Temp\" & Format(Now, "Report - yyyy-mm-dd hh-mm-ss") & ".pdf" but it didn't.

Can you help with that?

 

[sirrusja]

Hi eveybody!

I have found solution myself!
Just remove seconds value from the following lines:
Filename = "C:\Temp\Temp\" & Format(Now, "Report - yyyy-mm-dd hh-mm-ss") & ".pdf"
and
sAttachment = "C:\Temp\Temp\" & Format(Now, "Report - yyyy-mm-dd hh-mm-ss") & ".pdf"

So finally I can email PDF attachment from Excel 2003!!!