Adding Image to email

[charlee63]

I'm getting a Type mismatch at ".Attachments.Add MyPath & Pic". Can someone see what I am doing wrong?

Sub Email()
' Set Tabs
Dim WSX As Worksheet
Set WSX = Worksheets("Email")

Dim objNotif_Email
Dim dist_list
Dim email_from As String
Dim email_subject
Dim email_body1, email_body2
Dim filepath As String
Dim new_report
Dim Pic As String, MyPath As String

'Setting fields
email_from = WSX.Cells(1, "B")
dist_list = WSX.Cells(2, "B")
email_subject = WSX.Cells(3, "B")
email_body1 = WSX.Cells(5, "B").Value & "<p><p>" & _
"<font FACE=Calibri SIZE=3 style=""color:red""><b>" & WSX.Range("B6:B6") & "</font></b><p>" & _
WSX.Range("B7:B7") & "<p>" & _
WSX.Range("B8:B8") & "<p>" & _
WSX.Range("B9:B9") & "<p>" & _
WSX.Range("B10:B10") & "<p>"
email_body2 = WSX.Range("B17:B17") & "<p>" & _
WSX.Range("B18:B18") & "<p>" & _
"<font FACE=Calibri SIZE=3 style=""color:Blue""><b>" & WSX.Range("B19:B19") & "</font></b><p>" & _
WSX.Range("B20:B20") & "<p>" & _
"<font FACE=Calibri SIZE=3 style=""color:Blue""><b>" & WSX.Range("B21:B21") & "</font></b><p>" & _
WSX.Range("B22:B22") & "<p>" & _
"<font FACE=Calibri SIZE=3 style=""color:Blue""><b>" & WSX.Range("B23:B23") & "</font></b><p>" & _
WSX.Range("B24:B24") & "<p>" & _
"<font FACE=Calibri SIZE=3 style=""color:Blue""><b>" & WSX.Range("B25:B25") & "</font></b><p><p>" & _
WSX.Cells(27, "B").Value & "<p>" & WSX.Cells(28, "B").Value
MyPath = "I:\PERISHABLES\Merchandising\National Merchandising\Slotting\Template\"
Pic = "Complete.png"


' Doing email
Set objNotif_Email = CreateObject("CDO.Message")
With objNotif_Email
.From = email_from
.BCC = dist_list
.Subject = email_subject
.Attachments.Add MyPath & Pic
.HTMLBody = email_body1 & "<img src='cid:Complete.png'" & "height=520 width=750>" & email_body2

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Name or IP of Remote SMTP Server
.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp-relay.cswg.com"
.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "MerchandisingTeam"
.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "123"
.Configuration.Fields.Update
'==End remote SMTP server configuration section==
' objNotif_Email.Display Does not suppot
.send
End With
End Sub

 

[plettieri]

hello charlee63


Im not sure here, but you might try this:

The error you're encountering (Type mismatch) at .Attachments.Add MyPath & Pic is likely due to the Attachments.Add method expecting a valid file path, and there might be an issue with the way the path is being concatenated or the file itself.

Here are some steps to troubleshoot and fix the issue:

1. Check File Path and Name:
   • Ensure that MyPath & Pic correctly forms a valid file path.
   • You can use Debug.Print MyPath & Pic to print the full path to the Immediate Window in the VBA editor to verify it.
2. Ensure the File Exists:
   • Make sure the file Complete.png exists in the specified directory (I:\PERISHABLES\Merchandising\National Merchandising\Slotting\Template\).
3. Correctly Format the HTMLBody:
   • Ensure the HTML body correctly references the image.

Here's the revised code with added debug statements and some minor corrections:

vba

Sub Email()
' Set Tabs
Dim WSX As Worksheet
Set WSX = Worksheets("Email")

Dim objNotif_Email As Object
Dim dist_list As String
Dim email_from As String
Dim email_subject As String
Dim email_body1 As String, email_body2 As String
Dim filepath As String
Dim new_report As String
Dim Pic As String, MyPath As String

' Setting fields
email_from = WSX.Cells(1, "B").Value
dist_list = WSX.Cells(2, "B").Value
email_subject = WSX.Cells(3, "B").Value
email_body1 = WSX.Cells(5, "B").Value & "<p><p>" & _
"<font FACE=Calibri SIZE=3 style=""color:red""><b>" & WSX.Range("B6").Value & "</font></b><p>" & _
WSX.Range("B7").Value & "<p>" & _
WSX.Range("B8").Value & "<p>" & _
WSX.Range("B9").Value & "<p>" & _
WSX.Range("B10").Value & "<p>"
email_body2 = WSX.Range("B17").Value & "<p>" & _
WSX.Range("B18").Value & "<p>" & _
"<font FACE=Calibri SIZE=3 style=""color:Blue""><b>" & WSX.Range("B19").Value & "</font></b><p>" & _
WSX.Range("B20").Value & "<p>" & _
"<font FACE=Calibri SIZE=3 style=""color:Blue""><b>" & WSX.Range("B21").Value & "</font></b><p>" & _
WSX.Range("B22").Value & "<p>" & _
"<font FACE=Calibri SIZE=3 style=""color:Blue""><b>" & WSX.Range("B23").Value & "</font></b><p>" & _
WSX.Range("B24").Value & "<p>" & _
"<font FACE=Calibri SIZE=3 style=""color:Blue""><b>" & WSX.Range("B25").Value & "</font></b><p><p>" & _
WSX.Cells(27, "B").Value & "<p>" & WSX.Cells(28, "B").Value
MyPath = "I:\PERISHABLES\Merchandising\National Merchandising\Slotting\Template\"
Pic = "Complete.png"

' Debugging: Print the full path to the Immediate Window
Debug.Print MyPath & Pic

' Doing email
Set objNotif_Email = CreateObject("CDO.Message")
With objNotif_Email
.From = email_from
.BCC = dist_list
.Subject = email_subject

' Check if the file exists before adding it
If Dir(MyPath & Pic) <> "" Then
.Attachments.Add MyPath & Pic
Else
MsgBox "File not found: " & MyPath & Pic, vbExclamation
Exit Sub
End If

.HTMLBody = email_body1 & "<img src='cid:Complete.png' height='520' width='750'>" & email_body2

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Name or IP of Remote SMTP Server
.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp-relay.cswg.com"
.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "MerchandisingTeam"
.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "123"
.Configuration.Fields.Update
'==End remote SMTP server configuration section==
' objNotif_Email.Display Does not support
.send
End With
End Sub

Points to consider:​

1. Ensure the File Path and Name are Correct:
   • Added Debug.Print MyPath & Pic to print the full path to the Immediate Window for debugging purposes.
2. Check if the File Exists:
   • Added a check to see if the file exists before attempting to add it as an attachment. If the file does not exist, a message box will inform you, and the subroutine will exit.
3. Correct HTMLBody Formatting:
   • Adjusted the HTML body to ensure proper formatting of the image tag.

By adding these checks and debugging steps, you should be able to identify the issue more clearly and ensure that the file path is correct and the file exists before attempting to attach it.


hope this helps
plettieri