Send Email Function Run-time error 438

[Melhem]

Hello every one
I'm trying to send email using VBA code, the function is working and the email is sent, but the problem is when function end 'Run-time error 438 object doesn't support this property or method' appears
here is the code:

Public Function SendEmail(ItemName As String, Total_Qnty As Integer)
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    Set Flds = iConf.Fields
 
    ' send one copy with Google SMTP server (with autentication)
    schema = "schemas.microsoft.com/cdo/configuration/"
    Flds.Item(schema & "sendusing") = 2
    Flds.Item(schema & "smtpserver") = "smtp.gmail.com"
    Flds.Item(schema & "smtpserverport") = 465
    Flds.Item(schema & "smtpauthenticate") = 1
    Flds.Item(schema & "sendusername") = "[EMAIL="example@gmail.com"]example@gmail.com[/EMAIL]"
    Flds.Item(schema & "sendpassword") = "*****"
    Flds.Item(schema & "smtpusessl") = 1
    Flds.Update

    With iMsg
        .To = "[EMAIL="example@hotmail.com"]example@hotmail.com[/EMAIL]"
        .From = "[EMAIL="example@gmail.com"]example@gmail.com[/EMAIL]"
        .Subject = "Mail from gmail"
        .HTMLBody = "The Stock Safty Level of Item: " & ItemName & " is DOWN, The total quantity you have is: " & Total_Qnty & "!!"
        Set .Configuration = iConf
        .Send
    End With
 
    Set iMsg = Nothing
    Set iConf = Nothing
    Set Flds = Nothing
End Function

 

[pr2-eugin]

Hello Melhem, I have not worked with CDO, so I am not able to give you a straight forward answer.. It would be helpful if you could pin point where (which line) exactly you get this error.. How are you calling this function?

PS: Please use Code Tags when posting VBA Code

 

[JHB]

Where do you declare the 3 variables iMsg, iConf and Flds?
Use Option explicit in your code.

 

[ozinm]

Silly question - but I'll ask it anyway...
if I recall correctly (I haven't got access to Gmail at work) you need to enable your gmail account to allow access through smtp, pop3, imap, etc.

Has this been done?

 

[Melhem]

First thank you for your replay, and your PS
The error is at the end 'End Function'
I call the function like that : Print SendEmail(Me.Item.Value, Me.Total.Value)

Any way I changed the Function to Sub and it work
This is the solution :

Sub CDO_Mail_Small_Text_2()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant
     
   Dim strEmail As String

    strEmail = InputBox(Prompt:="Enter Destination Email please.", _
          Title:="ENTER Destination Email")

     
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
     
    iConf.Load -1 ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
    schema = "schemas.microsoft.com/cdo/configuration/"
    .Item(schema & "sendusing") = 2
    .Item(schema & "smtpserver") = "smtp.gmail.com"
    .Item(schema & "smtpserverport") = 465
    .Item(schema & "smtpauthenticate") = 1
    .Item(schema & "sendusername") = "example@gmail.com"
    .Item(schema & "sendpassword") = "*****"
    .Item(schema & "smtpusessl") = 1
    .Update
    End With
     
    strbody = "Hi there" & vbNewLine & vbNewLine & _
    "The Stock Safty Level of Item: " & Me.Item.Value & " is DOWN, The total quantity we have is: " & Me.Total.Value & "!!" & vbNewLine & vbNewLine & _
    "Please do the necessary"
     
    With iMsg
        Set .Configuration = iConf
        .To = strEmail
        .CC = ""
        .BCC = ""
        
        .From = "example@gmail.com"
        .Subject = "" & Me.Item.Value & " Safety Stock Alert"
        .TextBody = strbody
        .Send
    End With
     
End Sub

 

[Melhem]

Silly question - but I'll ask it anyway...
if I recall correctly (I haven't got access to Gmail at work) you need to enable your gmail account to allow access through smtp, pop3, imap, etc.

Has this been done?

Yes sure . like I mentioned the email is sent well , I don't know why there is a runtime error at the end .. any way I found another solution I write it down