将从Excel提取的循环文本插入电子邮件正文

mbzjlibv  于 2022-11-18  发布在  其他
关注(0)|答案(1)|浏览(124)

@Niton帮我解决了第一个问题,那就是如何从Excel文件中提取数据,这种方式会一直循环下去,直到找到一个新的电子邮件地址。它允许我从多行(以及这些行上的几个字段)中提取数据,并将其放入Outlook电子邮件中。
我现在的问题是,当它这样做的时候,我需要它被包括在一封电子邮件的正文中。所以会有一些文字,如问候,然后'你有这些优惠券,我们需要还清,请... EXCEL数据在这里...谢谢你看这个,这是你可以发送到的地址,如果你需要更新我们,给我们回邮件'。措辞是不完整的,将被修改,但这是一般的想法...将Excel文本放入电子邮件的正文中。我添加了一些字段,这些字段将被拉到strVoucher中,如代码所示。
我尝试过不同的迭代,因为一开始Excel信息只会沿着文本一遍又一遍地重复。然后我能够分离至少部分电子邮件代码,这样它就会放入第一段问候文本,但后来我被困在试图让它在Excel数据后添加更多的文本,而不是一遍又一遍地重复所有的文本。我试图添加另一个“与Outmail”部分,但这只是覆盖了整个电子邮件。
这是我现在的代码。谢谢@niton!

Option Explicit

Sub oneEmail_SortedEmailAddresses()

Dim OutApp As Object
Dim OutMail As Object

Dim strVoucher As String

Dim lr As Long

Set OutApp = CreateObject("Outlook.Application")

lr = ActiveSheet.UsedRange.Rows.Count
    
Dim toAddress As String
Dim i As Long
Dim refundDescYes As Boolean

Dim sigString As String
Dim strbody As String
Dim strname As String
Dim strname2 As String
Dim strCheckNbr As String
Dim strCheckDate As String
Dim strCheckAmt As String
Dim strCheckTst As String

Rows("1:6").Select
        Selection.Delete
        
        Range("A1:N1").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Check Reconciliation Status").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Check Reconciliation Status").AutoFilter.Sort. _
        SortFields.Add2 key:=Range("A1"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Check Reconciliation Status").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Rows("2:5").Select
    Selection.Delete Shift:=xlUp
    
    Range("i2") = "Yes"
        Range("I2").AutoFill Destination:=Range("I2:I" & lr)

For i = 2 To lr

Set OutApp = CreateObject("Outlook.Application")
    
    'sigString = Environ("appdata") &
               '"\Microsoft\Signatures\Uncashed Checks.htm"
    '           If Dir(sigString) <> "" Then
     '    signature = GetBoiler(sigString)
     '    Else
     '    signature = ""
     '   End If
        
    '    Select Case Time
     '      Case 0.25 To 0.5
     '           GreetTime = "Good morning"
     '      Case 0.5 To 0.71
     '           GreetTime = "Good afternoon"
     '      Case Else
     '           GreetTime = "Good evening"
     '   End Select
     
        
                   
        

    ' Email address
    If ActiveSheet.Range("N" & i).Value <> "" Then
    
        ' One email per email address
        ' This assumes the addresses are sorted
        If ActiveSheet.Range("N" & i).Value <> toAddress Then
        
            If Not OutMail Is Nothing Then
                If refundDescYes = True Then
                    OutMail.display
                Else
                    OutMail.Close 1 ' olDiscard
                End If
            End If
            
            toAddress = ActiveSheet.Range("N" & i).Value
            Debug.Print toAddress
            
            Set OutMail = Nothing
            refundDescYes = False
            
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
            
            strname = Cells(i, "A").Value
            strname2 = strname
            If InStr(Cells(i, "A"), ",") Then strname2 = Trim(Split(strname, ",")(1))
            
                .To = toAddress
                .Subject = "Open Vouchers"
                 
                 strbody = "<Font face = TimesNewRoman p style=font-size:18.5px color = #0033CC)<br><br>You are receiving this email because our records show you have vouchers open as follows:  " & _
                "<br><br>Voucher #:  " & strVoucher & _
                "<br>Check Date:  " & strCheckDate & _
                "<br>Check Amount:  " & strCheckAmt
                .HTMLBody = "<Font face = TimesNewRoman p style=font-size:26.5px color = #0033CC><B><p style=font-size:18.5px>Dear " & strname2 & ", " & strbody & "<br>"
                .HTMLBody = "<B><br><br>Please reply to this email with any questions." & _
             "<br><br>***If we do not receive a reply from you within the next 30 days, you will not be paid."
                
            End With
        End If
        
        ' Refund Desc
        If ActiveSheet.Range("I" & i).Value = "Yes" Then
        
            refundDescYes = True
            
            ' Voucher
            strCheckTst = "Check Number "
            strCheckNbr = Cells(i, "K").Value
            strVoucher = strCheckTst & Cells(i, "D").Value & " " & Cells(i, "K").Value
            
            strCheckDate = Cells(i, "L").Value
            strCheckAmt = Cells(i, "H").Value
                      
            With OutMail
           
            .HTMLBody = .HTMLBody & "<br>" & strVoucher
            
             End With
        End If

    End If
    
Next

If Not OutMail Is Nothing Then
    If refundDescYes = True Then
        OutMail.display
    Else
        OutMail.Close 1 ' olDiscard
    End If
End If

Set OutMail = Nothing

Debug.Print "Done."

End Sub

wgx48brx

wgx48brx1#

下面的示例***可能不起作用***因为您没有在工作表上发布数据的副本,所以我必须做一些假设。请将此示例用作如何组织代码的示例。
你的主要问题是你的代码的组织,包括循环内部和外部。在我的例子中,我通过将大块的代码拉到其他例程中来简化主要逻辑。这应该会使你的代码的整体“流程”更容易阅读和使用。
请注意以下几点:
1.对区域、工作表和工作簿始终使用fully qualify your references
1.避免magic numbers
将下面的代码修改为您自己的数据,看看是否有帮助。

Option Explicit

Sub Example()
    Dim statusWS As Worksheet
    Set statusWS = ThisWorkbook.Sheets("Check Reconciliation Status")
    PrepareData statusWS
    
    Const NAME_COL As Long = 1
    Const DATE_COL As Long = 12
    Const CHKNUM_COL As Long = 11
    Const AMT_COL As Long = 8
    Const TOADDR_COL As Long = 14
    
    '--- only do this once
    Dim outlookApp As Outlook.Application
    Set outlookApp = AttachToOutlookApplication
    
    With statusWS
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Dim i As Long
        For i = 2 To lastRow
            If .Cells(i, TOADDR_COL).Value <> vbNullString Then
                '--- create the email now that everything is ready
                Dim email As Outlook.MailItem
                Set email = outlookApp.AddItem(0)
                With email
                    .To = statusWS.Cells(i, TOADDR_COL)
                    .Subject = "Open Vouchers"
                    .HTMLBody = BuildEmailBody(statusWS.Cells(i, NAME_COL), _
                                          statusWS.Cells(i, DATE_COL), _
                                          statusWS.Cells(i, CHKNUM_COL), _
                                          statusWS.Cells(i, AMT_COL))
                    '--- send it now
                    '    (if you want to send it later, you have to
                    '     keep track of all the emails you create)
                    .Send
                End With
            End If
        Next i
    End With
    
End Sub

Sub PrepareData(ByRef ws As Worksheet)
    With ws
        .Rows("1:6").Delete
        .Range("A1:N1").AutoFilter
        .AutoFilter.Sort.SortFields.Clear
        .AutoFilter.Sort.SortFields.Add2 Key:=Range("A1"), SortOn:=xlSortOnValues, Order:= _
            xlAscending, DataOption:=xlSortTextAsNumbers
        With .AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        '.Rows("2:5").Delete Shift:=xlUp
        .Range("i2") = "Yes"
        
        '--- it only makes sense to find the last row after all the
        '    other prep and deletions are complete
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("I2").AutoFill Destination:=Range("I2:I" & lastRow)
    End With
End Sub

Function BuildEmailBody(ByVal name As String, _
                        ByVal checkDate As Date, _
                        ByVal checkNumber As Long, _
                        ByVal checkAmt As Double) As String

    Const body1 As String = "<Font face = TimesNewRoman p style=font-size:18.5px color = " & _
                            "#0033CC)"
    Const body2 As String = "<Font face = TimesNewRoman p style=font-size:18.5px color = " & _
                            "#0033CC)<br><br>You are receiving this email because our " & _
                            "records show you have vouchers open as follows:  "
    Const body3 As String = "<B><br><br>Please reply to this email with any questions." & _
                            "<br><br>***If we do not receive a reply from you within " & _
                            "the next 30 days, you will not be paid.<br><br>"
    Dim body As String
    body = body1 & TimeOfDayGreeting & name & "," & body2
    body = body & "<br><br>Voucher #:  " & checkNumber
    body = body & "<br>Check Date:  " & Format(checkDate, "dd-mmm-yyyy")
    body = body & "<br>Check Amount:  " & Format(checkAmt, "$#,##0.00")
    body = body & body3 & EmailSignature
    BuildEmailBody = body
End Function

Function EmailSignature() As String
    Dim sigCheck As String
    sigCheck = Environ("appdata") & "\Microsoft\Signatures\Uncashed Checks.htm"
    
    If Dir(sigCheck) <> vbNullString Then
        EmailSignature = GetBoiler(sigString)
    Else
        EmailSignature = vbNullString
    End If
End Function

Function TimeOfDayGreeting() As String
    Select Case Time
      Case 0.25 To 0.5
           TimeOfDayGreeting = "Good morning "
      Case 0.5 To 0.71
           TimeOfDayGreeting = "Good afternoon "
      Case Else
           TimeOfDayGreeting = "Good evening "
   End Select
End Function

Public Function OutlookIsRunning() As Boolean
    '--- quick check to see if an instance of Outlook is running
    Dim msApp As Object
    On Error Resume Next
    Set msApp = GetObject(, "Outlook.Application")
    If Err > 0 Then
        '--- not running
        OutlookIsRunning = False
    Else
        '--- running
        OutlookIsRunning = True
    End If
End Function

Public Function AttachToOutlookApplication() As Outlook.Application
    '--- finds an existing and running instance of Outlook, or starts
    '    the application if one is not already running
    Dim msApp As Outlook.Application
    On Error Resume Next
    Set msApp = GetObject(, "Outlook.Application")
    If Err > 0 Then
        '--- we have to start one
        '    an exception will be raised if the application is not installed
        Set msApp = CreateObject("Outlook.Application")
    End If
    Set AttachToOutlookApplication = msApp
End Function

相关问题