VBA Create Email with Excel-CodePudding

Here is the code that I have written, I have little experience with VBA so I am sure there is something stupid I am overlooking...any thoughts as to how to fix this? I keep getting the compile error: user defined type not defined. The yellow highlight is for this line:

Public Function SendMessage(strSubject, strRecip, strMsg, strAttachment) As Boolean

Blue highlight for this line:

Dim mItem As Outlook.MailItem

Any help is appreciated!!!

    Option Explicit

    Public Function SendMessage(strSubject, strRecip, strMsg, strAttachment) As Boolean

    Dim mOutlookApp As Object
    Dim mNameSpace As Object
    Set mOutlookApp = GetObject("", "Outlook.application")
    Set mNameSpace = mOutlookApp.GetNamespace("MAPI")
    Dim mFolder As Object
    Dim mItem As Outlook.MailItem

    Set mItem = mOutlookApp.CreateItem(0)
    mItem.To = "Americas"
    mItem.CC = strRecip
    mItem.SentOnBehalfOfName = "Jordan"
    mItem.Subject = strSubject
    mItem.Body = strMsg
    mItem.Attachments.Add strAttachment

    mItem.Display
    mItem.Recipients.ResolveAll

    End Function

    Sub Summarydraft()
    Dim result As Boolean
    
    Dim strRecip As String
    Dim strSubject As String
    Dim strMsg As String
    Dim strAttachment As String
    
    Dim LastRow As Long
    LastRow = Worksheets("Control").Cells(Rows.Count, "N").End(xlUp).Row
    Dim rng As Range, fullrng As Range
    Set fullrng = Worksheets("Control").Range("N3:N" & LastRow)
    Dim recip As String
    
      For Each rng In fullrng
            recip = recip & "; " & rng.Value
        Next
        
        strRecip = recip
        strSubject = Worksheets("Control").Range("G14")
        strMsg = Worksheets("Control").Range("G17")
        strAttachment = Worksheets("Control").Range("G20")
        result = SendMessage(strSubject, strRecip, strMsg, strAttachment)
    End Sub

CodePudding user response：

Either change the line

Dim mItem As Outlook.MailItem

Dim mItem As Object

Or add Outlook as a reference in your VBA project.

CodePudding user response：

You need to add an Outlook COM reference to your VBA project if you want to declare Outlook types in the code.

In the code you used the late binding technology. This technology uses either the Visual Basic GetObject function or the CreateObject function to initialize Outlook.

To use early binding, you first need to set a reference to the Outlook object library. Use the Reference command on the Visual Basic for Applications (VBA) Tools menu to set a reference to Microsoft Outlook xx.x Object Library, where xx.x represents the version of Outlook that you are working with. You can then use the following syntax to start an Outlook session.

Dim objOL as Outlook.Application 
Set objOL = New Outlook.Application

Read more about that in the Automating Outlook from a Visual Basic Application article.