MAIL.AddAttachment (Sheets("MAIL").Range("N17").Text) <---- Why is it not working?

[VBAzr]

Hi everyone,

Just trying to send an email (gmail) using VBA CDO in excel and my problem is in the title.

I want my mail attachment to be selected in a specific cell (that is doing a Vlookup on a customer name) in one of my sheets:

MAIL.AddAttachment (Sheets("MAIL").Range("N17").Text)

Thanks a lot!

 

[vds1]

Hi VBAzr,

If you have set MAIL as

set MAIL = CreateObject("CDO.Message")

then

MAIL.AddAttachment Sheets("MAIL").Range("N17").value

where cell N17 must be C:\username\CDO_Example.xlsm

 

[VBAzr]

Hi vds1,

That's what I did... still not working. Here is the code:

____________________________________________________
Private Sub btnSendEmail_Click()

Dim MAIL As New Message
Dim config As Configuration
Set config = MAIL.Configuration
Set MAIL = CreateObject("CDO.Message")

config(cdoSendUsingMethod) = cdoSendUsingPort
config(cdoSMTPServer) = "smtp.gmail.com"
config(cdoSMTPServerPort) = 25
config(cdoSMTPAuthenticate) = cdoBasic
config(cdoSMTPUseSSL) = True
config(cdoSendUserName) = "email@email.com"
config(cdoSendPassword) = "password"
config.Fields.Update

Call createJpg("MAILDELAY.FR", "A1:I49", "INVOICE")
tempfilepath = Environ$("temp") & "\"
MAIL.AddAttachment tempfilepath & "INVOICE.jpg", olbyvalue, 1

MAIL.To = Range("N14").Text
MAIL.CC = Range("N15").Text
MAIL.From = config(cdoSendUserName)
MAIL.Subject = "Pénalité " + Range("E17").Text + " sur délai de livraison // " + Range("N4").Text
MAIL.HTMLBody = "<span LANG=FR><p class=style2 p align=justify p style='width='850' height='1500'><span LANG=FR><font FACE=Calibri SIZE=3>" & _
"<p>dear, blah blah blah...<p/>"

MAIL.AddAttachment Sheets("MAILDELAY.FR").Range("N17").Value

On Error Resume Next

MAIL.Send

If Err.Number <> 0 Then
MsgBox Err.Description, vbCritical, "There was an error"
Exit Sub

End If

MsgBox "your email has been sent", vbInformation, "sent"


End Sub
_______________________________________________________
Sub createJpg(Namesheet As String, nameRange As String, nameFile As String)
ThisWorkbook.Activate
Worksheets(Namesheet).Activate
Set Plage = ThisWorkbook.Worksheets(Namesheet).Range(nameRange)
Plage.CopyPicture
With ThisWorkbook.Worksheets(Namesheet).ChartObjects.Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height)
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
End With
Worksheets(Namesheet).ChartObjects(Worksheets(Namesheet).ChartObjects.Count).Delete
Set Plage = Nothing
End Sub

 

[vds1]

Hi VBAzr,

Few questions for you ,

1) have you tried to open the file manually mentioned in c17? have you ensured the name and file path is accurate ?
2) Just curious to know what value you have in C17 at the moment? have you tried for a different file path and name ?
3) What is the error message ?

here is a good resource for you
Sending mail from Excel with CDO

 

[VBAzr]

1) have you tried to open the file manually mentioned in c17? have you ensured the name and file path is accurate ?
Yes. And it works if I put it like that:
MAIL.AddAttachment "C:\thefilepath.pdf"

2) Just curious to know what value you have in C17 at the moment? have you tried for a different file path and name ?
It is the file path (from a Vlookup)

3) What is the error message ?
Run-time error '-2147024894 (80070002)':
file not found.

Thanks for the link!

 

[VBAzr]

I really can't solve my problem... Even if I'm using the information in the linked you suggested me.

 

[VBAzr]

up

 

[VBAzr]

up

 

[VBAzr]

up