# Email attachment.add cell value runtime error 13 type mismatch



## Kjuri (Dec 2, 2018)

[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 (Dec 2, 2018)

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 (Dec 3, 2018)

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 (Dec 3, 2018)

And if you replace S12 by the file name?


----------



## Kjuri (Dec 3, 2018)

Same result.


----------



## Kjuri (Dec 12, 2018)

Has anybody else suggestions?


----------



## Kamolga (Dec 12, 2018)

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 (Dec 12, 2018)

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 (Dec 12, 2018)

```
"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 (Dec 12, 2018)

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.


----------



## Kjuri (Dec 2, 2018)

[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 (Dec 13, 2018)

Kjuri said:


> 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.



When I put adress I use 
	
	
	
	
	
	



```
.[COLOR=#333333]Attachments.Add ("D:\8\AAA.xls")[/COLOR]
```
 with brackets, I don't know if they are mandatory.

And you have also an error with

```
Dim AttWb as workbook
set Attwb=Workbooks.Open("D:\8\AAA.xls")



.Attachments.Add Attwb.FullName[/FONT][COLOR=#3D3D3D][FONT='inherit']
[/FONT][/COLOR]
```
?


----------



## Kamolga (Dec 13, 2018)

Hi,

I finally could test it (with outlook). I saved a file in c:\8 called AAA in format 2007 (.xls). I put AAA in S12 and this worked:


```
Sub Mail_attacment()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Set sh = Sheets("Sheet1")
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .to = sh.Range("I13").Value
                .Subject = sh.Range("S11").Value
                .Body = sh.Range("O8").Value
                .Attachments.Add "C:\8\" & sh.Range("S12").Value & ".xls"
                .Display
            End With
    Set OutMail = Nothing
    Set OutApp = Nothing
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub
```


----------



## Kjuri (Dec 13, 2018)

Hi Kamolga! 

How can I use this code for Outlook in my case? 

Back to your previous comment. Do you want me to try it with code like: 


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


      Dim AttWb As Workbook
Set AttWb = Workbooks.Open("D:\8\AAA.xls")


.Attachments.Add AttWb.FullName


End With
```

? 

If yes, than it did not work too.


----------



## Kamolga (Dec 13, 2018)

The outlook code works (just change C: with D: ) if you have outlook (part of Microsoft Office, not the online version) set up on your computer: this code sends from default address but it it can be canged if you have several adresses.

To send emails from Excel, you need to go in VBA, click tools menu, then references... and tick Microsoft Outlook xxx object library (xxx is 16.0 in my case).


----------

