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]

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]

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]

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]

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