Hi guys! I'm new in vba coding here. Need some help for this error.
I try to send mail excel sheets. But got some error here. Please help me for this error Run-time error '-2147024773 (8007007b)': The filename, the directory name, or volume label syntax is incorrect.
Function sendEmail(SenderName As String, SenderMail As String, ToPerson As String, CcPerson As String, subjectMail As String, mailContent As String) As Boolean 'object to attach
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim iMsg As Object
Dim iConf As Object
Dim Flds As Variant
Dim I As Long
sendEmail = True
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
'Production Email Setting
recipientEmail = "[email protected]"
iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "my server here"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With
strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"
TempFilePath = Environ$("desktop") & "\"
TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
.Close SaveChanges:=True
End With
With iMsg
Set .Configuration = iConf
.To = recipientEmail
.CC = CcPerson
.BCC = ""
.From = SenderName & "<" & SenderMail & ">"
.Subject = "Excel(" & Format(Now(), "dd/MM/yyyy") & ")"
.TextBody = mailContent
.addattachment (TempFilePath & TempFileName)
.Send
End With
'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Application.Wait (Now() + TimeValue("00:00:02"))
End Function
Thanks
Regards
st3vi3
Bookmarks