Attachments to Emails

[MrChips]

Hi there.

I am attempting to use the following code to attach a .pdf file to an email:-
#######################################
Dim strTo As String
Dim strSubject As String
Dim strBody As String
Dim strPathAttach As String
Dim objOutlook As Object
Dim objMailItem As Object
Const olMailItem As Integer = 0
Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(olMailItem)
strTo = Forms!f2_Advised!Text43
objMailItem.To = strTo
strSubject = Forms!f2_members_details!Full_name & " - Charitable status statement"
objMailItem.subject = strSubject
strBody = Forms!f2_email_text!Email_Body_Text
objMailItem.body = strBody

strPathAttach = Forms!f2_Advised!Text29 '= "R:\charity\Statements\Parker David Fi-31Aug12_email.pdf" '
With objMailItem.Attachments
.Add strPathAttach
End With

objMailItem.Send
##############################################
I am able to send out the email ... but .... the attachment will not attach
I get the error


"Cannot find the file"
"Verify the path and filename are correct"
This happens on the .Add strPathAttach line of code.

If I cut and paste the file name (as shown in the comments) into my Windows File Explorer, it loads Adobe and displays the file perfectly.

In desperation I have tried all sorts of permutations of enclosing qoutes , putting in equals signs ... adding commas ... pure desperation! ... all to no avail.

Can anyone point me to the cause of this aberration please?

[GeekInOhio]

Try changing:

strPathAttach = Forms!f2_Advised!Text29

to

strPathAttach = Forms!f2_Advised.Text29

[MrChips]

strPathAttach = Forms!f2_Advised.Text29

[/QUOTE]


I was very excited when I saw this suggestion, it being about the only permutation that I hadn't thought of trying.

Regret, it didn't work. ... but many thanks for spending the time looking at it for me.

I think I might have to resort to 'snailmail'

[June7]

I tested code to send email with PDF attachment and it worked perfectly.

Try a test of code with literal string instead of the textbox variable.

This is my code:

Sub emailTest()
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "email address here"
    ''.cc = ""
    ''.bcc = ""
    .Subject = "test"
    .HTMLBody = "PDF attached"
    .Attachments.add ("C:\test.pdf")
    .DeleteAfterSubmit = True 'to not save in sent bin
    ''.Display
    .Send
End With

[John_G]

On thing you can do is put a msgbox Forms!f2_Advised!Text29 line right before the strpathattach = line, to verify that Forms!f2_Advised!Text29 really does contain the value it should.

John

[MrChips]

On thing you can do is put a msgbox Forms!f2_Advised!Text29 line right before the strpathattach = line, to verify that Forms!f2_Advised!Text29 really does contain the value it should.

John

John, A debug.print confirms that the file name is thre and correct.
... also, if I cut and paste that into the Windows7 File explorer, it brings up the file in Adboe.
So I am pretty confident that that is OK.

[MrChips]

June7.

I like your clean code and will, if I ever get this thing going, implement it .
It is so much easier to read.

However, it still will not work for me.

I am beginning to wonder if there is some problem in my Outlook2010 environment that is refusing to accept the attachment

Any Outlook experts out there?

Many thanks for the help already given ... it is really appreciated.

[MrChips]

June7

Thank you for your input.
I liked the clean appearance and elegance of your code, and rewrote mine accordingly.
(True plagiarism) ... and I love it --- Ta.

However, I still had exactly the same problem , and couldn't shake it.

Bizarre as it is, I eventually got it going by adding this

Dim FileName as String
FileName = Forms!f2_Advised.Text29
and
.Attachments.Add FileName

.... and now it works

For some reason it wanted go go via a simple variable!

Many thanks for your code ... and all of your time and effort... all of you

[June7]

My code is essentially the same code you already had but looks cleaner only because I am not using a lot of variables. I routinely don't use variables if they will be referenced only once. Reference to the form control is usually adequate. However, you encountered situation where that was not true. Still odd but as long as it works...

If Outlook had been the issue, error message probably would have been different. For a while I couldn't get this code to work on my home pc. Kept telling me Outlook needed to be configured. Just tried again today and it worked so something in my pc environment changed.