asked on

Mail attachment an Excel file in VBA

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

AFGPHXExcel

ASKER

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.

Anil

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

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

Hope this helps.

A>

Arno Koster

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 ?

ASKER CERTIFIED SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial