CODEWITHSUNDEEP
vbaexcel
0xtuytuy
0xtuytuy

Reputation: 1654

attachement in email VBA excel

I am trying to send an email through vba in excel, all works fine excpect the email attachement. It doesnt seem to link it. Where could be the issue ? The string attach is pointing to the right file.

Dim OutApp As Object
Dim OutMail As Object
Dim email
Dim attach

email = writeEmailAddress()
attach = attachement()

Sheets("Mail").Range("B1") = email
Sheets("Mail").Range("B2") = "xxxxxx"
Sheets("Mail").Range("B3") = "xxxxxxx"
Sheets("Mail").Range("B4") = attach
MsgBox attach

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
    .SendKeys "^{ENTER}"
    .to = "xxxxx"
    .CC = ""
    .BCC = ""
    .Subject = Sheets("Mail").Range("B5").Value
    .Body = Sheets("Mail").Range("B6").Value
    'You can add other files also like this
    .Attachments.Add attach ' <--------------------------------This is causing troubble
    .Display 
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Upvotes: 1

Views: 535

Answers (1)

user4039065
user4039065

Reputation:

Change,

.Attachments.Add attach

... to,

If CBool(Len(Dir(attach, vbNormal))) Then
    .Attachments.Add attach, 1   '<~~ 1 is olByValue 
Else
    Debug.Print "Cannot find '" & attach & "'"
End If

If the attachment is not added to your email item, check the VBE's Immediate Window (e.g. Ctrl+G) for the error message.

Upvotes: 2

Related Questions