Hi All,
I've managed to create a macro whereby it saves individual worksheets as a separate PDF but I need some help with the next step.
Once the worksheet has been saved as a PDF, I would like Outlook to open up, create a new email with the individual attachment.
This is what I have so far:
Sub ExportToPDFs()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim oApp As Object
Dim oMail As Object
For Each ws In Worksheets
ws.Select
nm = ws.Name
If Not ws.Name = "Master" _
And Not ws.Name = "Lookups" _
And Not ws.Name = "TB" _
And Not ws.Name = "Store Lookups" Then
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:="X:\IT\Test\test\" & nm & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = [I1]
.Subject = "test"
.body = "This is a test"
.Attachments.Add 'This is where the macro fails and I understand why but not to sure how to select the nearly created worksheet'
End With
End If
Next ws
Application.ScreenUpdating = True
End Sub
Any suggestions would be most grateful.
Thanks
Karima
Bookmarks