Sending PDF with Outlook

[Kari]

Hi,

I'm using Ron de Bruin's "Mail_ActiveSheet_PDF_Outlook" macro.
How I must to change the code that macro go to pick addresses from table
data from column J
and stop receiving mail when there is no anymore addresses in column J in
table data?

Greetings,
Kari


Sub Mail_ActiveSheet_PDF_Outlook()
'Note: It is easy to change the code to send a workbook, selection or range.
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FilenameStr As String
If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then
FilenameStr = Application.DefaultFilePath & "\" & _
Format(Now, "dd-mmm-yy") & ".pdf"
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
strbody = "Hello" & vbNewLine & vbNewLine & _
"Here is to you latest attachment" & vbNewLine & _
vbNewLine & "t. Kari"
On Error Resume Next
With OutMail
.To = "here comes data from table data column J"
.CC = ""
.BCC = ""
.Subject = "Last list"
.Body = strbody
.Attachments.Add FilenameStr
.Send 'or use .Display
End With
On Error GoTo 0
'Delete the pdf you send
Kill FilenameStr
Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "PDF add-in Not Installed"
End If
End Sub

 

[Ron de Bruin]

Hi Kan

See
http://www.rondebruin.nl/mail/tips2.htm