Email attachment.add cell value runtime error 13 type mismatch

[Kjuri]

[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]

 

[Kamolga]

It can be linked to the extension. Try to pass by a string to maybe avoid missmatch error
Example:

[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]

 

[Kjuri]

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?

 

[Kamolga]

And if you replace S12 by the file name?

 

[Kjuri]

Same result.

 

[Kjuri]

Has anybody else suggestions?

 

[Kamolga]

Could you open the file you want to attach, open vba then CTRL+G (to open immediate window) and run this in a module?

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?

 

[Kjuri]

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

 

[Kamolga]

"D:\8" [FONT=Verdana]&Range("S12").value & ".xlsx"[/FONT]

had no chance to work, it should have been

"D:\8\" [FONT=Verdana]& Range("S12").value & ".xls"[/FONT]

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

[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?

 

[Kjuri]

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.