Email attachment.add cell value runtime error 13 type mismatch

Kjuri

New Member
Joined
Dec 2, 2018
Messages
8
[h=1]Hi!


I would like to attach different types of file to my emails. This code worked well and sent emails untill I placed the attachment adding.


.Attachments.Add "D:\8" & Range("S12").Value


After that it was stucked on runtime error 13 type mismatch.


Can anybody help me with this?






Sub Send_Mail2()
'
' Send_Mail2 Makró
'




'creating a CDO object
Dim Mail As CDO.Message
Set Mail = New CDO.Message




'Enable SSL Authentication
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True




'SMTP authentication Enabled=true (1)
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1




'SMTP server and port Details
'Get these details from the Settings Page of your Gmail Account
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"smtp.gmail.com"
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2




'Credentials of Gmail Account
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = _
"username@gmail.com"
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = _
"password"




'Update the configuration fields
Mail.Configuration.Fields.Update




'Email Properties
With Mail
.subject = Range("S11").Value
.From = "username@gmail.com"
.To = Range("I13").Value
.CC = ""
.BCC = ""
.TextBody = Range("O8").Value
.Attachments.Add "D:\8" & Range("S12").Value


End With
'to send the mail
Mail.Send




End Sub
[/h]
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
It can be linked to the extension. Try to pass by a string to maybe avoid missmatch error
Example:
Code:
[COLOR=#303336][FONT=inherit]Dim [/FONT][/COLOR][COLOR=#303336]strLocation as string 
[/COLOR][COLOR=#303336][FONT=inherit]strLocation [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"D:\8\" [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Range("S12").value [/FONT][/COLOR][COLOR=#303336][FONT=inherit]& [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]".xlsx"
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Attachments[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Add [/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]strLocation[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR]
 
Last edited:
Upvote 0
Dear Kamolga,

I have add your code like this:

'Email Properties
With Mail
.subject = Range("S11").Value
.From = "username@gmail.com"
.To = Range("I13").Value
.CC = ""
.BCC = ""
.TextBody = Range("O8").Value

Dim
strLocation as string
strLocation ="D:\8" & Range("S12").value & ".xlsx"
.Attachments.Add (strLocation) - now the mismatch error appears on this line
Do you have any other suggestion?
 
Upvote 0
Could you open the file you want to attach, open vba then CTRL+G (to open immediate window) and run this in a module?
Code:
Sub test()
[LEFT][COLOR=#666666][FONT="Courier New"]MsgBox ActiveWorkbook.FullName
debug.print [LEFT][COLOR=#666666][FONT="Courier New"]ActiveWorkbook.FullName[/FONT][/COLOR][/LEFT]
End sub[/FONT][/COLOR][/LEFT]

Could you paste here what came in the immediate window (or message box) as well as S12 value of the other file?
 
Upvote 0
I replaced

.Attachments.Add "D:\8" & Range("S12").Value

with

.Attachments.Add "D:\8\AAA.xls"

The error still appeared. Then made your test.

The message box contains the following:

Compile error

Invalid use of property
 
Upvote 0
Code:
"D:\8" [FONT=Verdana]&Range("S12").value & ".xlsx"[/FONT]
had no chance to work, it should have been
Code:
"D:\8\" [FONT=Verdana]& Range("S12").value & ".xls"[/FONT]

For the sub, I don't know why it jumped a line on pasting

Code:
[LEFT][COLOR=#333333][FONT=monospace]Sub test()
[/FONT][/COLOR][COLOR=#333333][FONT=monospace][COLOR=#666666][FONT=Courier New]MsgBox ActiveWorkbook.FullName
debug.print [COLOR=#666666][FONT=Courier New]ActiveWorkbook.FullName[/FONT][/COLOR]
End sub[/FONT][/COLOR][/FONT][/COLOR][/LEFT]

Is D: a vpn network, external drive or something with login/password?
 
Last edited:
Upvote 0
Dear Kamolga,

the code is ok. As I wrote you, I created a blank excel file named AAA.xls, and replaced the code .Attachments.Add "D:\8" & Range("S12").Value with .Attachments.Add "D:\8\AAA.xls" to avoid any mistakes about this code.

D: is a hardrive on my comp. Without any login.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top