VBA run code based off cell value

[floggingmolly]

I have a workbook that generates a PDF and email based off of templates on another sheet. I have a dropdown that lists the templates, and based off of the selection, the code loops through the data and creates a PDF letter and an email. This works, but I have 4 different VBA codes for each of the 4 letters. The email body and template is different for each of the 4 letters. I have to do each letter type separately. Does anybody have any suggestions how I could load the data for all letter types, and have the correct code run based off of the template specified for that row? For example, in cell B3 it lists the template selected from the dropdown, but if I had the template number listed for each row, then the code would look at the value in column B for the specific row and use that code. Then as it loops through each row it would use the template number listed in column B. Hope this makes sense. Below is the code I have for one of the letters/emails. Not sure if it's possible to do this or not but thought I'd see what the pros here think.

Sub NotApproved()
    Dim CustRow, CustCol, lastRow, TemplRow, DaysSince, FrDays, ToDays As Long
    Dim DocLoc, TagName, TagValue, TemplName, FileName As String
    Dim CurDt, LastAppDt As Date
    Dim WordDoc As Object
    Dim WordApp As Object
    Dim OutApp As Object
    Dim OutMail As Object
    Dim WordContent As Object ' You may need to update this if it's not Range
    Dim WordFind As Object
    Dim PauseTime As Double

    ' Define Word constants
    Const wdFindContinue As Integer = 1 ' Change this to the correct value

    ' Set up Word application
    On Error Resume Next ' If Word is already running
    Set WordApp = GetObject("Word.Application")
    If Err.Number <> 0 Then
        ' Launch a new instance of Word
        Err.Clear
        Set WordApp = CreateObject("Word.Application")
        ' WordApp.Visible = True ' Make the application visible to the user
    End If
  
  With ThisWorkbook.Sheets("LETTER MAKER") ' Replace "Sheet1" with the actual sheet name
    If IsEmpty(.Range("B3").Value) Then
        MsgBox "Please select a correct template from the drop down list"
        .Range("D3").Select
        Exit Sub

  End If
    TemplRow = .Range("B3").Value 'Set Template Row
    TemplName = .Range("D3").Value 'Set Template Name
    FrDays = .Range("K3").Value 'Set From Days
    ToDays = .Range("M3").Value 'Set To Days
    DocLoc = Sheet2.Range("F" & TemplRow).Value 'Word Document Filename
    
    'Open Word Template
    On Error Resume Next 'If Word is already running
    Set WordApp = GetObject("Word.Application")
    If Err.Number <> 0 Then
    'Launch a new instance of Word
    Err.Clear
    'On Error GoTo Error_Handler
    Set WordApp = CreateObject("Word.Application")
    WordApp.Visible = True 'Make the application visible to the user
    
    
    End If
    
    
    lastRow = .Range("E500").End(xlUp).Row  'Determine Last Row in Table
        For CustRow = 8 To lastRow
                DaysSince = .Range("S" & CustRow).Value
                If TemplName <> .Range("S" & CustRow).Value And DaysSince >= FrDays And DaysSince <= ToDays Then
                                Set WordDoc = WordApp.Documents.Open(FileName:=DocLoc, ReadOnly:=False) 'Open Template
                                For CustCol = 1 To 19 'Move Through 9 Columns
                                    TagName = .Cells(7, CustCol).Value 'Tag Name
                                    TagValue = .Cells(CustRow, CustCol).Value 'Tag Value
                                     With WordDoc.Content.Find
                                        .Text = TagName
                                        .Replacement.Text = TagValue
                                        .Wrap = wdFindContinue
                                        .Execute Replace:=wdReplaceAll 'Find & Replace all instances
                                     End With
                                Next CustCol
                        
                         If .Range("F3").Value = "PDF" Then
                                       FileName = ThisWorkbook.Path & "\PDF files\" & "\" & .Range("I" & CustRow).Value & " - " & .Range("E" & CustRow).Value & " - NOT APPROVED" & ".pdf" 'Create full filename & Path with current workbook location, Last Name & First Name
                                       WordDoc.ExportAsFixedFormat OutputFileName:=FileName, ExportFormat:=wdExportFormatPDF
                                                                                                                    
                                       WordDoc.Close False
                                       
                                   Else: 'If Word
                                       FileName = ThisWorkbook.Path & "\word files\" & "\" & .Range("I" & CustRow).Value & " - " & .Range("E" & CustRow).Value & ".docx"
                                       WordDoc.SaveAs FileName
                                       PauseTime = Now + TimeValue("0:00:03")
                                        Do
                                         DoEvents
                                            Loop Until Now >= PauseTime
                                     
                                   End If
                                   .Range("T" & CustRow).Value = TemplName 'Template Name
                                   .Range("U" & CustRow).Value = Now
                                    If .Range("H3").Value = "Email" Then
                                                  Set OutApp = CreateObject("Outlook.Application") 'Create Outlook Application
                                                  Set OutMail = OutApp.CreateItem(0) 'Create Email
                                                  With OutMail
        .SentOnBehalfOfName = "Myemail@mail.com"
        .To = Sheet1.Range("V" & CustRow).Value
        .Subject = "Approval for " & Sheet1.Range("I" & CustRow).Value & "  " & Sheet1.Range("F" & CustRow).Value & " " & Sheet1.Range("H" & CustRow).Value

        ' Replace plain text body with HTML-formatted body
        .HTMLBody = Sheet1.Range("D" & CustRow).Value & "/" & Sheet1.Range("C" & CustRow).Value & "<BR>" & Sheet1.Range("I" & CustRow).Value & " " & Sheet1.Range("E" & CustRow).Value & _
            "<br>ISP Agreement: " & Sheet1.Range("F" & CustRow).Value & "<br>CSA:" & Sheet1.Range("H" & CustRow).Value & _
            "<br><br><b>Not Approved for Negotiations</b>" & _
            "<p style='margin: 0; padding: 0;'>This would be the body for the first letter.</p>" 
           



        .Attachments.Add FileName
        .Display 'To send without Displaying change .Display to .Send
        Application.Wait (Now + TimeValue("0:00:3"))
    End With
                                    Else:
                                           
                                           
                                          WordDoc.Close False
                                          
                                    End If
                       
            End If '3 condition met
        Next CustRow
        WordApp.Quit
End With
End Sub

 

[igold]

I am sorry, but I do not have an answer to your question. However, as a note to you, when you are declaring your variables, you need to specify each variable... I am only showing your first line but the same holds true for you second and third line of declarations...

  Dim CustRow, CustCol, lastrow, TemplRow, DaysSince, FrDays, ToDays As Long
    'Is really declaring your variables like this:
    Dim CustRow As Variant
    Dim CustCol As Variant
    Dim lastrow As Variant
    Dim TemplRow As Variant
    Dim DaysSince As Variant
    Dim FrDays As Variant
    Dim ToDays As Long
    
    'What you are really trying to do is this:
    Dim CustRow As Long, CustCol As Long, lastrow As Long, TemplRow As Long, DaysSince As Long, FrDays As Long, ToDays As Long

It's probably transparent to you when you run your code, but I thought I would mention it...