admin管理员组

文章数量:1327471

I have code that fills an Outlook email template with different variables. e.g. name, ID number, etc.
It works with both ".oft" and ".msg" templates stored on OneDrive.

I tried to alter the code so that it calls one of two templates depending on a variable.
It often gives me

Error 91 object variable or with block variable not set

on mailItem.Display right after I call a template.

Note: I'd rather do two "If - Then" statements than an "If - Then - Else" statement because there are three variable types and only two require email generation.

It worked for one variable type:

Do While Sheet1.Cells(r, 7) <> ""
    'Call Outlook template
    Set mailApp = CreateObject("Outlook.Application")
    Set mailItem = mailApp.CreateItemFromTemplate("filepath\template.msg")
    mailItem.Display

When I added the If statements, the 91 error appeared on mail.Item.Display:

Do While Sheet1.Cells(r, 7) <> ""
    Set mailApp = CreateObject("Outlook.Application")
    If Sheet1.Cells(r, 4) = "Type A" Then Set mailItem = mailApp.CreateItemFromTemplate("filepath\Atemplate.msg")
    If Sheet1.Cells(r, 4) = "Tyep B" Then Set mailItem = mailApp.CreateItemFromTemplate("filepath\Btemplate.msg")
    mailItem.Display <<<<<ERROR

Later in the code the If statements work for different ".Subjects".

With mailItem
    .Display
    If Sheet1.Cells(r, 4) = "Type A" Or Sheet1.Cells(r, 4) = "Type B" Then .To = Toemail
    If Sheet1.Cells(r, 4) = "Type A" Then .Subject = "Login Information for " + Firstname + " " + Lastname + "
    If Sheet1.Cells(r, 4) = "Type B" Then .Subject = "Your other Information is ready - " + Firstname + " " + Lastname

I tried just one If statement (and made sure that Type A was the top row in Excel). No change.

I tried moving the EmpID = Sheet1.Cells... code chunk above Set mailApp.... No change.

I tried Removing Set from Set mailItem in both If statements. No change.

I tried moving the mailItem.Display to the end of each If statement, but the error moved down to my variables (EmpID...)

Do While Sheet1.Cells(r, 7) <> ""
    Set mailApp = CreateObject("Outlook.Application")
    If Sheet1.Cells(r, 4) = "Type A" Then Set mailItem = mailApp.CreateItemFromTemplate("filepath\Atemplate.msg") And mailItem.Display
    If Sheet1.Cells(r, 4) = "Type B" Then Set mailItem = mailApp.CreateItemFromTemplate("filepath\Btemplate.msg") And mailItem.Display
    
    EmpID = Sheet1.Cells(r, 1)     <<<<<<ERROR
    Lastname = Sheet1.Cells(r, 2)
    Firstname = Sheet1.Cells(r, 3)
    UserID = Sheet1.Cells(r, 5)
    EmailID = Sheet1.Cells(r, 6)
    Toemail = Sheet1.Cells(r, 7)

I tried setting Sheet1.Cells (r, 4) as a String variable Dim VariableType = String VariableType = Sheet1.Cells(r, 4).

If it's related, I could never get it to go r+1 for one variable.
Tried:

VariableType = Sheet1.Cells(r, 4)
If VariableType = "Type C" Then r = r + 1`

and

If Sheet1.Cells(r, 4) = "Type C" Then r = r + 1

Full code that works:

Sub Send_email_from_template_my_code_1()

Dim EmpID As String
Dim Lastname As String
Dim Firstname As String
Dim VariableType As String
Dim UserID As String
Dim EmailID As String
Dim Toemail As String
Dim FromEmail As String

Dim mailApp As Object
Dim mailItem As Object
R=row number, and r = r + 1 will change the row number.
Dim r As Long
r = 2

Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object

VariableType = Sheet1.Cells(r, 4)
If VariableType = "Type C" Then r = r + 1

Do While Sheet1.Cells(r, 7) <> ""
    'Call Outlook template
    Set mailApp = CreateObject("Outlook.Application")
    Set mailItem = mailApp.CreateItemFromTemplate("filepath\template.msg")
    mailItem.Display
    
    EmpID = Sheet1.Cells(r, 1)
    Lastname = Sheet1.Cells(r, 2)
    Firstname = Sheet1.Cells(r, 3)
    UserID = Sheet1.Cells(r, 5)
    EmailID = Sheet1.Cells(r, 6)
    Toemail = Sheet1.Cells(r, 7)
    
    With mailItem
        .Display
        If Sheet1.Cells(r, 4) = "Type A" Or Sheet1.Cells(r, 4) = "Type B" Then .To = Toemail
        If Sheet1.Cells(r, 4) = "Type A" Then .Subject = "Login Information for " + Firstname + " " + Lastname + "
        If Sheet1.Cells(r, 4) = "Type B" Then .Subject = "Your other Information is ready - " + Firstname + " " + Lastname

        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="[NAME]")
                oRng.Text = Firstname + " " + Lastname
            Loop
        End With
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="[EmpID]")
                oRng.Text = EmpID
            Loop
        End With
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="[EmailID]")
                oRng.Text = EmailID
            Loop
        End With
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="[UserID]")
                oRng.Text = UserID
            Loop
        End With
        
    End With
    
    mailItem.Display

    r = r + 1
Loop

End Sub

I have code that fills an Outlook email template with different variables. e.g. name, ID number, etc.
It works with both ".oft" and ".msg" templates stored on OneDrive.

I tried to alter the code so that it calls one of two templates depending on a variable.
It often gives me

Error 91 object variable or with block variable not set

on mailItem.Display right after I call a template.

Note: I'd rather do two "If - Then" statements than an "If - Then - Else" statement because there are three variable types and only two require email generation.

It worked for one variable type:

Do While Sheet1.Cells(r, 7) <> ""
    'Call Outlook template
    Set mailApp = CreateObject("Outlook.Application")
    Set mailItem = mailApp.CreateItemFromTemplate("filepath\template.msg")
    mailItem.Display

When I added the If statements, the 91 error appeared on mail.Item.Display:

Do While Sheet1.Cells(r, 7) <> ""
    Set mailApp = CreateObject("Outlook.Application")
    If Sheet1.Cells(r, 4) = "Type A" Then Set mailItem = mailApp.CreateItemFromTemplate("filepath\Atemplate.msg")
    If Sheet1.Cells(r, 4) = "Tyep B" Then Set mailItem = mailApp.CreateItemFromTemplate("filepath\Btemplate.msg")
    mailItem.Display <<<<<ERROR

Later in the code the If statements work for different ".Subjects".

With mailItem
    .Display
    If Sheet1.Cells(r, 4) = "Type A" Or Sheet1.Cells(r, 4) = "Type B" Then .To = Toemail
    If Sheet1.Cells(r, 4) = "Type A" Then .Subject = "Login Information for " + Firstname + " " + Lastname + "
    If Sheet1.Cells(r, 4) = "Type B" Then .Subject = "Your other Information is ready - " + Firstname + " " + Lastname

I tried just one If statement (and made sure that Type A was the top row in Excel). No change.

I tried moving the EmpID = Sheet1.Cells... code chunk above Set mailApp.... No change.

I tried Removing Set from Set mailItem in both If statements. No change.

I tried moving the mailItem.Display to the end of each If statement, but the error moved down to my variables (EmpID...)

Do While Sheet1.Cells(r, 7) <> ""
    Set mailApp = CreateObject("Outlook.Application")
    If Sheet1.Cells(r, 4) = "Type A" Then Set mailItem = mailApp.CreateItemFromTemplate("filepath\Atemplate.msg") And mailItem.Display
    If Sheet1.Cells(r, 4) = "Type B" Then Set mailItem = mailApp.CreateItemFromTemplate("filepath\Btemplate.msg") And mailItem.Display
    
    EmpID = Sheet1.Cells(r, 1)     <<<<<<ERROR
    Lastname = Sheet1.Cells(r, 2)
    Firstname = Sheet1.Cells(r, 3)
    UserID = Sheet1.Cells(r, 5)
    EmailID = Sheet1.Cells(r, 6)
    Toemail = Sheet1.Cells(r, 7)

I tried setting Sheet1.Cells (r, 4) as a String variable Dim VariableType = String VariableType = Sheet1.Cells(r, 4).

If it's related, I could never get it to go r+1 for one variable.
Tried:

VariableType = Sheet1.Cells(r, 4)
If VariableType = "Type C" Then r = r + 1`

and

If Sheet1.Cells(r, 4) = "Type C" Then r = r + 1

Full code that works:

Sub Send_email_from_template_my_code_1()

Dim EmpID As String
Dim Lastname As String
Dim Firstname As String
Dim VariableType As String
Dim UserID As String
Dim EmailID As String
Dim Toemail As String
Dim FromEmail As String

Dim mailApp As Object
Dim mailItem As Object
R=row number, and r = r + 1 will change the row number.
Dim r As Long
r = 2

Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object

VariableType = Sheet1.Cells(r, 4)
If VariableType = "Type C" Then r = r + 1

Do While Sheet1.Cells(r, 7) <> ""
    'Call Outlook template
    Set mailApp = CreateObject("Outlook.Application")
    Set mailItem = mailApp.CreateItemFromTemplate("filepath\template.msg")
    mailItem.Display
    
    EmpID = Sheet1.Cells(r, 1)
    Lastname = Sheet1.Cells(r, 2)
    Firstname = Sheet1.Cells(r, 3)
    UserID = Sheet1.Cells(r, 5)
    EmailID = Sheet1.Cells(r, 6)
    Toemail = Sheet1.Cells(r, 7)
    
    With mailItem
        .Display
        If Sheet1.Cells(r, 4) = "Type A" Or Sheet1.Cells(r, 4) = "Type B" Then .To = Toemail
        If Sheet1.Cells(r, 4) = "Type A" Then .Subject = "Login Information for " + Firstname + " " + Lastname + "
        If Sheet1.Cells(r, 4) = "Type B" Then .Subject = "Your other Information is ready - " + Firstname + " " + Lastname

        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="[NAME]")
                oRng.Text = Firstname + " " + Lastname
            Loop
        End With
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="[EmpID]")
                oRng.Text = EmpID
            Loop
        End With
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="[EmailID]")
                oRng.Text = EmailID
            Loop
        End With
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="[UserID]")
                oRng.Text = UserID
            Loop
        End With
        
    End With
    
    mailItem.Display

    r = r + 1
Loop

End Sub
  • excel
  • vba
  • outlook
  • email-templates
Share Improve this question edited Feb 6 at 0:00 CommunityBot 11 silver badge asked Dec 12, 2024 at 20:09 AhamannAhamann 33 bronze badges 2
  • 4 Tyep B ? Typo . – CDP1802 Commented Dec 12, 2024 at 20:17
  • 1 there are 3 variable types and only 2 require email generation. You need to put the email creation code in an IF block so that you can skip over it for the Type C. Also you only need to create Oulook.Application object once, not a new one for every email – CDP1802 Commented Dec 12, 2024 at 20:28
Add a comment  | 

3 Answers 3

Reset to default 1

Untested as I don't have Outlook

Option Explicit
Sub Send_email_from_template_my_code_1()

    Dim EmpID As String, Lastname As String, Firstname As String
    Dim VariableType As String, UserID As String
    Dim EmailID As String, Toemail As String, FromEmail As String
    
    Dim mailApp As Object, mailItem As Object
    Set mailApp = CreateObject("Outlook.Application")
    
    Dim olInsp As Object, wdDoc As Object, oRng As Object
    Dim sText As String, r As Long, template As String
    
    r = 2
    Do While Sheet1.Cells(r, 7) <> ""
        
        VariableType = Sheet1.Cells(r, 4)
        If VariableType = "Type A" Then
            template = "Atemplate.msg"
            sText = "Login Information for "
        ElseIf VariableType = "Type B" Then
            template = "Btemplate.msg"
            sText = "Your other Information is ready - "
        Else
            template = ""
        End If

        If template <> "" Then
            With Sheet1
                EmpID = .Cells(r, 1)
                Lastname = .Cells(r, 2)
                Firstname = .Cells(r, 3)
                UserID = .Cells(r, 5)
                EmailID = .Cells(r, 6)
                Toemail = .Cells(r, 7)
            End With
    
            'Call Outlook template
            Set mailItem = mailApp.CreateItemFromTemplate("filepath\" & template)
            With mailItem
                .To = Toemail
                .Subject = sText & Firstname & " " & Lastname
                .display
      
                Set olInsp = .GetInspector
                Set wdDoc = olInsp.WordEditor
                Set oRng = wdDoc.Range
                With oRng.Find
                    Do While .Execute(FindText:="[NAME]")
                        oRng.Text = Firstname & " " & Lastname
                    Loop
                End With
                Set oRng = wdDoc.Range
                With oRng.Find
                    Do While .Execute(FindText:="[EmpID]")
                        oRng.Text = EmpID
                    Loop
                End With
                Set oRng = wdDoc.Range
                With oRng.Find
                    Do While .Execute(FindText:="[EmailID]")
                        oRng.Text = EmailID
                    Loop
                End With
                Set oRng = wdDoc.Range
                With oRng.Find
                    Do While .Execute(FindText:="[UserID]")
                        oRng.Text = UserID
                    Loop
                End With
                
            End With
        End If
        r = r + 1
    Loop
    Set mailApp = Nothing
End Sub

You already got a suggestion but I'd also add this.

You can create a sub to do the find and replace

'Replace text `placeHolder` with `txt` in Word Range `rng`
Sub ReplaceRangeText(rng As Object, placeHolder As String, txt As String)
    With rng.Find
        Do While .Execute(FindText:=placeHolder)
            rng.Text = txt
        Loop
    End With
End Sub

Then you can do (eg):

    With mailApp.CreateItemFromTemplate(templatePath)
        .Display
        .Subject = Subj
        .To = rw.Cells(7).Value
            
        Set wdDoc = .GetInspector.WordEditor
        ReplaceRangeText wdDoc.Range, "[NAME]", FullName
        ReplaceRangeText wdDoc.Range, "[EmpID]", rw.Cells(1).Value   'EmpID
        ReplaceRangeText wdDoc.Range, "[EmailID]", rw.Cells(6).Value 'EmailID
        ReplaceRangeText wdDoc.Range, "[UserID]", rw.Cells(5).Value  'UserID
    End With

Thanks everyone for your help! Below is my new, complete working code:

Sub ReplaceRangeText(rng As Object, placeHolder As String, txt As String)
With rng.Find
    Do While .Execute(FindText:=placeHolder)
        rng.Text = txt
    Loop
End With
End Sub

Sub Send_email_from_template_my_code_1()

Dim EmpID As String, Lastname As String, Firstname As String
Dim VariableType As String, UserID As String
Dim EmailID As String, Toemail As String, FromEmail As String

Dim mailApp As Object, mailItem As Object
Set mailApp = CreateObject("Outlook.Application")

Dim olInsp As Object, wdDoc As Object, oRng As Object
Dim sText As String, r As Long, template As String

r = 2
Do While Sheet1.Cells(r, 7) <> ""
    
    VariableType = Sheet1.Cells(r, 4)
    If VariableType = "Type A" Then
        template = "TestTemplate.msg"
        sText = "Your Information is ready - "
    ElseIf VariableType = "Type B" Then
        template = "TestTemplateB.msg"
        sText = "Different Subject - "
    Else
        template = ""
    End If

    If template <> "" Then
        With Sheet1
            EmpID = .Cells(r, 1)
            Lastname = .Cells(r, 2)
            Firstname = .Cells(r, 3)
            UserID = .Cells(r, 5)
            EmailID = .Cells(r, 6)
            Toemail = .Cells(r, 7)
        End With

    With mailApp.CreateItemFromTemplate("filepath" + template)
        .Display
        .Subject = sText + Firstname + " " + Lastname
        .To = Toemail
        
        Set wdDoc = .GetInspector.WordEditor
        ReplaceRangeText wdDoc.Range, "[NAME]", Firstname + " " + Lastname
        ReplaceRangeText wdDoc.Range, "[EmpID]", EmpID
        ReplaceRangeText wdDoc.Range, "[EmailID]", EmailID
        ReplaceRangeText wdDoc.Range, "[UserID]", UserID
    End With
        
        
    End If
    r = r + 1
Loop
Set mailApp = Nothing
End Sub

本文标签： excelCall one of two templates depending on a variableStack Overflow

版权声明：本文标题：excel - Call one of two templates depending on a variable - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1742209335a2433414.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。