Mail attachment an Excel file in VBA

AFGPHXExcel
AFGPHXExcel used Ask the Experts™
on
I am trying to attach a file I have saved to the desktop to an Outlook Object. Outlook seems to ignore the file though. Any help would be much appreciated.

v_WksFldrDest = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator

EmailAttachDest= v_WksFldrDest & v_TempFileName

            Dim OutApp As Object
            Dim OutMail As Object
            Dim strbody As String
       
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
               
                strbody = Range(ReviewerName & "ReqBy_EmailTxt").Value & vbNewLine & vbNewLine & _
                        "Please reply to: " & Range(ReviewerName & "ReplyTo_Name").Value & _
                            " at " & Range(ReviewerName & "ReplyTo_Email").Value & " and CC to " & Range(ReviewerName & "ReplyCC_Email").Value


            On Error Resume Next
            With OutMail
                .To = Range(GKName & "_GatekeeperEmail").Value
                .CC = Range(ReviewerName & "ReqCC_Email").Value
                .BCC = ""
                .Subject = v_TempFileName
                .Body = strbody
                'You can add a file like this
                .Attachments.Add (EmailAttachDest)
                '.Send or use
                .Display
            End With
            On Error GoTo 0
       
            Set OutMail = Nothing
            Set OutApp = Nothing
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
As a note. I am getting an error saying it cannot find the file even though the address for EmailAttachDest is correct and the file is on the desktop.
AnilData Manager

Commented:
The reference for Excel and Sending mail is from Ron BRuin MVP:

http://www.rondebruin.nl/sendmail.htm

Hope this helps.

A>
If I run it the variable 'EmailAttachDest' has the value "\[filename]" : no path is added.
Have you verified that the actual path to your desktop is included at your workplace ?
When indeed the desktop folder function does not work as expected, try to replace it with
v_WksFldrDest = Environ("USERPROFILE") & "\Desktop"

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial