VBA To Send PDF Files from Specific Folder

[sbpersson]

Good afternoon all,

I'm in the process of developing a macro that separately e-mails all PDF files in a folder to a distribution list. I've created a form to capture the various e-mail addresses and have set up the macro to initiate when users select the OK command button. I managed to get it to work perfectly sending to just one e-mail address at a time, but when I use my DistList variable it skips to End Sub.

Any help will be greatly appreciated!

Sub OKButton_Click()

RecEmail1 = Email1.Value
RecEmail2 = Email2.Value
RecEmail3 = Email3.Value
RecEmail4 = Email3.Value
RecEmail5 = Email3.Value

Dim objol As Object
Dim objmail As Object
Dim objFolder As Object
Dim strFolder As String
Dim fso As Object
Dim fsFolder As Object
Dim fsFile As Object
Dim DistList


DistList = Array(RecEmail1, RecEmail2, RecEmail3, RecEmail4, RecEmail5)


'// Create a folder browser. Note: You can change the last arg (the Empty) to a //
'// string where you want the folder browser to start in, such as: ThisWorkbook.Path//
Set objFolder = CreateObject("Shell.Application"). _
BrowseForFolder( _
0, "Select the folder that the workbooks are in.", 0, Empty)

On Error GoTo errhndl
If Not objFolder Is Nothing Then
'// Get the path to the folder user picked. //
strFolder = objFolder.Items.Item.Path
Else
'// In case user cancels folder browser //
MsgBox "Error picking a folder.", 0, ""
Exit Sub
End If

'// Create various needed objects. I happen to use late-binding. //
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsFolder = fso.GetFolder(strFolder)
Set objol = CreateObject("Outlook.Application")

For Each fsFile In fsFolder.Files


Set objmail = objol.CreateItem(0) '(olMailItem)

With objmail
.To = DistList
.Subject = "SCLS Invoice"
.Body = "Please see attached invoice. Thank you!"
.NoAging = True

If fsFile.Name Like "*.pdf" Then
.Attachments.Add strFolder & "" & fsFile.Name
End If
.Send
End With

Next


errhndl:
Set objFolder = Nothing
Set fso = Nothing
Set fsFolder = Nothing
Set objol = Nothing
Set objmail = Nothing
CancelButton_Click


End Sub

 

[Logit]

.
If the following represent Cells on a Worksheet, you need to specify the cell .Value, not the variable.

[COLOR=#333333]DistList = Array([B]RecEmail1, RecEmail2, RecEmail3, RecEmail4, RecEmail5[/B])[/COLOR]

Ex: DistList = Array(Sheets("Sheet1").Range("A1").Value, Sheets("Sheet1").Range("A2".Value, etc. etc. )

- or -

Dim RecEmail1 As String, RecEmail2 As String, RecEmail3 As String, RecEmail4 AS String, RecEmail5 As String

RecEmail1 = Sheets("Sheet1").Range("A1").Value
RecEmail2 = Sheets("Sheet1").Range("A2").Value
etc

DistList = Array(RecEmail1, RecEmail2, RecEmail3, RecEmail4, RecEmail5)


If the email addresses are not going to change, you can simply put the email addresses in separated by commas.

.To = me@yahoo.com, you@yahoo.com, them@yahoo.com etc. etc.

 

[sbpersson]

Hi there,

Thanks for responding! The variables do not correspond to data in cells however. Their values are populated through the use of the form i created. I do wish could just hard code the e-mail addresses, however, they will change each time this code is ran.

 

[Logit]

.
Disable your error handler to see where it errors out. Work from there.