excel - 将多行提取到 Outlook 电子邮件

Question

我有以下代码，当您突出显示一行并运行宏时，它会打开一封新的 Outlook 电子邮件并将相关数据粘贴到位，让客户知道他们的包裹已被发送。

Sub GalleryTracking()
  'This assumes that Outlook is already open to simplify the code
  '
  'The 'Font Name' and 'Font Size' attributes are variables obtained from the Spreadsheet

  Dim OutApp As Object
  Dim OutMail As Object
  Dim var As Variant: var = Selection.Value
  Dim sBody As String
  Dim sFontName As String
  Dim sFontSize As String

  'Set the Font Name and Font Size
  sFontName = "Calibri"
  sFontSize = "11"


  'Get the Email Body from the cell
  sBody = "Hey" & " " & var(1, 1) & vbCrLf & vbCrLf & _
          "Your item has been dispatched. Please find the tracking details below (Please allow 12hrs for tracking to go live): " & vbCrLf & vbCrLf & _
          "consignment number: " & var(1, 26) & vbCrLf & vbCrLf & "Please track here:  " & " https://www.tnt.com/express/en_gb/site/shipping-tools/tracking.html?searchType=con&cons=" & var(1, 26) & _
           vbCrLf & vbCrLf & _
          "IMPORTANT: Please note that when signing for the goods you must ensure you're satisfied with the packaging, if it looks damaged in any way please sign for as 'damaged'. 
   Please open your parcel with great care, particularly when using a knife to ensure that it does 
   not cut too deeply into the parcel damaging the item. You must inspect the goods promptly and 
    relay any issues to us within 24 hours, quoting reference " & var(1, 3) & "." & vbCrLf & 
   vbCrLf 
    & _
    "Have a great day, very best regards,"
    'Replace ASCII NEW LINE with HTML NEW LINE
    sBody = Replace(sBody, vbCrLf, "<BR>")                    'Converts text body to HTML
    sBody = Replace(sBody, vbLf, "<BR>")
    sBody = Replace(sBody, vbCr, "<BR>")


   'Attempt to create an Outlook object
    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then
    Err.Clear
    MsgBox "NOTHING DONE.  The Outlook Object could not be created from Excel." & vbCrLf & _
           "Try again when Outlook is open."
     Exit Sub

     End If
     On Error GoTo 0


    'Create the Outlook Mail Object (using the default Email account)
    Set OutMail = OutApp.CreateItem(0)

    'Grab the Signature
    OutMail.Display                 'Creates .HTMLbody containing the signature

    'Determine the values to be sent
    With OutMail
    .To = var(1, 6)
    .CC = var(1, 13)
       .Subject = "Your Order Has Been Dispatched"

    'Put New .HTMLbody (containing font information)  around sBody (HTML body) and in front of 
    the signature .HTMLBody
    ' .HTMLBody = "<p style='font-family:" & Arial & ";13:" & sFontSize & "pt'>" & sBody & " 
    </p>" & .HTMLBody    'Original - did not compile
      sFontName = "Calibri"
      sFontSize = "11"
     .HTMLBody = "<p style='font-family:" & sFontName & ";font-size:" & sFontSize & "pt'>" & 
    sBody & "</p>" & .HTMLBody

    .Display

    '.Send - comment out the 'Display line' if you want to send
   End With

   'Clear the Object Pointers
   Set OutMail = Nothing
   Set OutApp = Nothing



   End Sub

我们现在将以不同的方式来做这件事，并联系分支并提供一份已派出的物品列表。所以我需要做类似的事情，这样当我突出显示一组行并运行它时，它会显示类似于“以下客户项目已被调度”的内容，然后是客户名称列表、他们的参考和他们的跟踪号. 让我们说这些字段位于 A、B 和 C 列中以使其更容易。

电子邮件地址将类似于上面它位于单元格中的位置，因此如果它可以保留为数据被提取，而不是静态的，那就太好了。

标签： excelvbaemail