Method or Data Member not Found

[imfarhan]

Hi I have two functiions,
Cheque() whic simple check the reorcd in the table , whic reports need to sent email,

The 2nd function GenerateEmail() which giving me the error. "Method or Data Member not found.

I though the reference library missing but I already added the following library i.e.
-MS Outlook 12.0 Object Libray
-MS Office 12.0 Object Libaray
-others

The 2nd Function where the error come(red fonts) is as follows:

Please help me have I still miss any object or method?

Function GenerateEmail(sTo As String, sCC As String, sSubject As String, sBodyText As String, Optional sAttachment As String = "") As Boolean
    
    
    Dim NewMessage As Outlook.MailItem
    Dim objOutlook As New Outlook.Application
    Dim objNamespace As Outlook.Namespace
    
    On Error GoTo Err_Handler
    
    Set objNamespace = objOutlook.GetNamespace("MAPI")
    Set NewMessage = Application.[COLOR=red]CreateItem([/COLOR]olMailItem)
    
    With NewMessage
       
        .SentOnBehalfOfName = "Information2, BNHFT"
        .To = sTo
        .CC = sCC
        .Subject = sSubject
        .BodyFormat = olFormatHTML
        .Body = sBodyText
        
        If sAttachment <> "" Then
            .Attachments.Add Source:=sAttachment
        End If
        
        .DeleteAfterSubmit = True
        .Send
        DoEvents
    
    End With
        
    Set NewMessage = Nothing
    GenerateEmail = True
    
Exit Function
Err_Handler:
    MsgBox "Error: " & Err.Number & " - " & Err.Description, vbOKOnly, "ERROR!"
    NewMessage.Close olDiscard
    GenerateEmail = False
    
End Function

Many thanks
Farhan

 

[Norie]

Application refers to the application the code us running in, Excel.

You need to replace it with the reference you create earlier to the Outlook application, objOutlook.

 

[imfarhan]

Hi
I have change the code, but still the same error.
Please advise .

F

    'Dim NewMessage As Outlook.MailItem
    'Dim objOutlook As New Outlook.Application
    'Dim objNamespace As Outlook.Namespace
    
    Dim objOutlook As New Outlook.Application
    Dim objNamespace As Outlook.Namespace
    Dim objMail As Outlook.MailItem
    
    On Error GoTo Err_Handler
    
    Set objNamespace = objOutlook.GetNamespace("MAPI")
    Set NewMessage = Application.CreateItem(olMailItem)
    
    With objMail
    'NewMessage
'        .SentOnBehalfOfName = "[EMAIL="bnhft.Information@bnhft.nhs.uk"]bnhft.Information@bnhft.nhs.uk[/EMAIL]"
'        .SentOnBehalfOfName = "BNHFT, information2"
        .SentOnBehalfOfName = "Information2, BNHFT"
        
        .To = sTo
        .CC = sCC
        .Subject = sSubject
        .BodyFormat = olFormatHTML
        .Body = sBodyText
        
        If sAttachment <> "" Then
            .Attachments.Add Source:=sAttachment
        End If

 

[Norie]

You don't seem to have changed the code, you still have this:

Set NewMessage = Application.CreateItem(olMailItem)

As I said Application refers to the application the code is in, not Outlook.

 

[imfarhan]

Thanks Norie , sorry I'm stupid I'm not good to create and set objects

Now I have change , sorry again same error..
I have also save the module , re run but again same error.
I would appreciate if you help me on that. I have copied the code from net.
but actullay when I do Clth+Tab , I can't see the object = CreateItem.
What do you think?
Many thanks and regards
F

   Function GenerateEmail(sTo As String, sCC As String, sSubject As String, sBodyText As String, Optional sAttachment As String = "") As Boolean
    
    
    'Dim NewMessage As Outlook.MailItem
    'Dim objOutlook As New Outlook.Application
    'Dim objNamespace As Outlook.Namespace
    
    Dim objOutlook As New Outlook.Application
    Dim objNamespace As Outlook.Namespace
    Dim objMail As Outlook.MailItem
    
    On Error GoTo Err_Handler
    
    Set objNamespace = objOutlook.GetNamespace("MAPI")
    Set objMail = Application.[B]CreateItem[/B](olMailItem)
    
    With objMail
    'NewMessage
'        .SentOnBehalfOfName = "[EMAIL="bnhft.Information@bnhft.nhs.uk"]bnhft.Information@bnhft.nhs.uk[/EMAIL]"
'        .SentOnBehalfOfName = "BNHFT, information2"
        .SentOnBehalfOfName = "Information2, BNHFT"
        
        .To = sTo
        .CC = sCC
        .Subject = sSubject
        .BodyFormat = olFormatHTML
        .Body = sBodyText
        
        If sAttachment <> "" Then
            .Attachments.Add Source:=sAttachment
        End If
        
        .DeleteAfterSubmit = True
        .Send
        DoEvents
    
    End With

 

[imfarhan]

I think Ihave found the problem , appologies I will past the right code in a min . I need to test properly
Many thanks

 

[Norie]

Change Application to objOutlook.

 

[imfarhan]

Thanks a lot Norie,
I have checked offline application its working perfectly fine yes, you're rigth I have haven't give the reference when I try to set the objective as follows :-

    Set objNamespace = objOutlook.GetNamespace("MAPI")
    Set objMail = objOutlook.CreateItem(olMailItem)

Thanks again for your swift response.
Kind regards
Farhan