File attachment error sending CDO mail with VBA

[mexmanz]

Hello,

One last piece of the jigsaw to make the Userform/File work. I have a problem attaching a file with CDO mail.

Having used Ron de Bruin’s code for sending CDO mail, everything works fine apart from when I try attaching the newly saved excel file of a single sheet?

The code below is kept in a Module of the workbook and gets stuck at the .AddAttachment line each time.

How can I get around this please to attach the correct excel file required?

Error =
Run-time error ‘-2147024894(80070002)’:
The system cannot find the file specified.



‘----------------------
Sub CDO_Mail_Form()

'Code to send email
Dim iMsg As Object
Dim iConf As Object
Dim iCode As String
Dim fName As String
Dim NewFile As String
Dim Flds As Variant

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
= "123.456.789.0"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With

iCode = Worksheets("DataChangeRequestForm").Range("C4").Value
fName = ("\\FILE1\Data Change Request - " & iCode)
NewFile = fName & " " & Format(Date, "dd-mm-yyyy")

With iMsg
Set .Configuration = iConf
.To = "contact@mail.com"
.CC = ""
.BCC = ""
.From = "contact2@mail.com"
.Subject = "Change Request: " & iCode & " - (Main Category = " & frmDataChangeRequest.cboMainCat.Value & ")"
.TextBody = "Data Change Request Form Attached - " & iCode

‘Gets stuck at this line!! I am using the NewFile for path & name. Have also tried with 'FileExtStr' removed?.
.AddAttachment NewFile & FileExtStr

.Send
End With
End Sub
‘------------------------

‘In the form code: this is used to save the file as follows, which works fine:

Sub cmdSaveSend_Click()

'Code to save file as unique name with date stamp (iCode = Product Code)
Sheets("DataChangeRequestForm").Select
Sheets("DataChangeRequestForm").Protect Password:="password"
Sheets("DataChangeRequestForm").Copy

iCode = Worksheets("DataChangeRequestForm").Range("C4").Value
fName = ("\\FILE\DEPT\Shared Files by User\User\Forms\Change Request\Autosave\Data Change Request - " & iCode)
NewFile = fName & " " & Format(Date, "dd-mm-yyyy")

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=NewFile
Application.DisplayAlerts = True

'Run Module code to send email
CDO_Email_Change_Request_Form.CDO_Mail_Form

End Sub
‘----------------------------




Thank you very much for your help.

 

[mexmanz]

Note:

Please ignore the different fName details, they are identical in my file, just updated for posting purpose and didn't make them the same! sorry.

fName = ("\\FILE1\Data Change Request - " & iCode)

fName = ("\\FILE\DEPT\Shared Files by User\User\Forms\Change Request\Autosave\Data Change Request - " & iCode)

 

[mexmanz]

I have now resolved this.

Should have applied ALL the code together in the module!

works a treat now.

Thanks for looking

regards