如何在VBA中编写指定签名的Sub函数生成Outlook邮件并填充内容

Here's the fully implemented Sub function that fills the email body with your specified text plus all the provided parameters. I've included both plain text and HTML body options so you can choose based on your needs:

Sub GenerateTaskEmail(taskName As String, myGroup As String, myFile As String, myPer As String, RelatedTasks() As String)
    Dim olApp As Outlook.Application
    Dim m As Outlook.MailItem
    Dim bodyText As String
    Dim relatedTasksList As String
    Dim i As Integer
    
    ' Initialize Outlook application instance
    Set olApp = New Outlook.Application
    Set m = olApp.CreateItem(olMailItem)
    
    ' Build a formatted list of related tasks for plain text
    relatedTasksList = vbCrLf & "Related Tasks:" & vbCrLf
    For i = LBound(RelatedTasks) To UBound(RelatedTasks)
        relatedTasksList = relatedTasksList & "- " & RelatedTasks(i) & vbCrLf
    Next i
    
    ' Construct the full plain text body
    bodyText = "Hello All," & vbCrLf & vbCrLf & _
               "Please find the following information:" & vbCrLf & vbCrLf & _
               "Task Name: " & taskName & vbCrLf & _
               "Group: " & myGroup & vbCrLf & _
               "File: " & myFile & vbCrLf & _
               "Person: " & myPer & vbCrLf & _
               relatedTasksList
    
    With m
        .Display ' Shows the email window immediately
        .To = "somewhere@someplace.com" ' Update this to your recipient(s)
        .Subject = "Test Events" ' Adjust subject as needed
        
        ' Use this line for plain text body
        .Body = bodyText
        
        ' Uncomment the block below and comment the .Body line if you want HTML formatting
        '.HTMLBody = "<p>Hello All,</p>" & _
        '            "<p>Please find the following information:</p>" & _
        '            "<ul>" & _
        '            "<li>Task Name: " & taskName & "</li>" & _
        '            "<li>Group: " & myGroup & "</li>" & _
        '            "<li>File: " & myFile & "</li>" & _
        '            "<li>Person: " & myPer & "</li>" & _
        '            "</ul>" & _
        '            "<p>Related Tasks:</p>" & _
        '            "<ul>" & GetHtmlRelatedTasks(RelatedTasks) & "</ul>"
    End With
    
    ' Clean up objects to prevent memory leaks
    Set m = Nothing
    Set olApp = Nothing
End Sub

' Helper function to build an HTML unordered list from the RelatedTasks array
Function GetHtmlRelatedTasks(tasksArray() As String) As String
    Dim htmlList As String
    Dim i As Integer
    htmlList = ""
    
    For i = LBound(tasksArray) To UBound(tasksArray)
        htmlList = htmlList & "<li>" & tasksArray(i) & "</li>"
    Next i
    
    GetHtmlRelatedTasks = htmlList
End Function

Key Notes: #

• References: Ensure you have the Microsoft Outlook Object Library referenced in your VBA project. Go to Tools > References and check the box for Microsoft Outlook XX.X Object Library (XX.X will be your installed version).
• Plain Text vs HTML: The default uses plain text, but you can switch to HTML by uncommenting the .HTMLBody block and commenting out .Body. The HTML version uses proper formatting like paragraphs and bulleted lists.
• RelatedTasks Array: When calling this Sub, make sure your RelatedTasks array is properly initialized. For example:

  Dim myTasks(1 To 3) As String
  myTasks(1) = "Complete initial draft"
  myTasks(2) = "Review with stakeholders"
  myTasks(3) = "Finalize and submit"
  GenerateTaskEmail("Q3 Report", "Marketing", "Q3_Marketing_Report.pdf", "John Doe", myTasks)
  

• Cleanup: The code includes object cleanup to release Outlook resources, which is a good practice to avoid leaving unnecessary processes running.

内容的提问来源于stack exchange，提问作者Gopal Krishna