PDA

View Full Version : HELP!! Attachments to email - excel VBA



best1
02-22-2007, 07:20 AM
guys - having real struggles getting vba to attach pdf file from file path indicated in a txtBox on a userform

if you have any ideas please let me know - it's driving me nuts!!

heres relevant code:




On Error GoTo cleanup
For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then
Set OutMail = OutApp.CreateItem(0)
Dim attach As String
attach = txtAttach1.Value


On Error Resume Next
With OutMail
.To = cell.Value
.Cc = txtCc.Value
.Bcc = txtBcc.Value
.Subject = txtSubject.Value
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & txtBodyText.Value



.Attachments.Add attach & "\*.pdf"
.Send
End With
On Error GoTo 0


Set OutMail = Nothing
End If
Next cell



Any help would be great!


thanks!


best1

mvidas
02-22-2007, 09:13 AM
Hi best1,

It won't allow you to add attachments using wildcards like *.pdf, but what you can do is retrieve the .pdf filenames in your path one at a time and add them: Dim attach As String, vFile As String
attach = txtAttach1.Text
'add \ after path
If Right(attach, 1) <> "\" Then attach = attach & "\"

'your code

'add attachments
vFile = Dir(attach & "*.pdf")
Do Until Len(vFile) = 0
.Attachments.Add attach & vFile
vFile = Dir
Loop
'rest of your codeLet me know if you have any questions!
Matt

best1
02-23-2007, 01:41 AM
Matt

many thanks for that - works wonders!!

best1

mvidas
02-23-2007, 08:21 AM
Glad to help! Please let me know if you need anything else!

Also, as this is your first question here (Welcome!), you can mark this as solved by going to Thread Tools at the top of the page, and choosing Mark Thread Solved.