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