79403977

Date: 2025-01-31 20:56:10
Score: 0.5
Natty:
Report link

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
Reasons:
  • Blacklisted phrase (0.5): Thanks
  • Long answer (-1):
  • Has code block (-0.5):
  • Self-answer (0.5):
  • Low reputation (1):
Posted by: Ahamann