- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我的代码当前会根据 H 列中的姓名进行检查并创建电子邮件。因此 Approver1 会收到一封针对其所有人员的电子邮件。我已经用它来消除重复的员工姓名。示例:审批者 1 收到一封电子邮件,内容为“请批准以下所有员工的时间:”,然后有一个姓名列表...Sample1、Sample2 和 Sample3。该工作表通常会为每个审批者提供欺骗员工,如上面我的工作表所示。
该代码对于第一组重复名称效果很好(连续最多可能有 10 个相同的批准者,所有批准者都收到一封电子邮件),然后在任何单个名称中都可以正常运行。
当它遇到下一组重复批准者时,它会跳过该组中的第一行,然后为每个其他部门创建电子邮件;因此它会跳过一行,直到到达欺骗批准者部分的末尾。因此,从工作表中,批准者 1 会收到他的电子邮件,然后批准者 2 会收到她的电子邮件,但随后批准者 3 就会变得一团糟。
如何让它正确循环整个列表,为每个审批者创建一封电子邮件,并且其人员的所有相应姓名仅列出一次?
Sub DivisionApprovals()
Dim OutApp As Object
Dim OutMail As Object
Dim cell, lookrng As Range
Dim strdir As String
Dim strFilename As String
Dim sigString As String
Dim strBody As String
Dim strName As Variant
Dim strName1 As Variant
Dim strDept As Variant
Dim strName2 As String
Dim strbody2 As String
Dim strName3 As Variant
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set rng = ActiveSheet.UsedRange
r = 2
Do While r <= rng.rows.count
Set OutMail = OutApp.CreateItem(0)
Set strName = rng.Cells(r, 1)
Set strName3 = rng.Cells(r, 3)
strName2 = Trim(Split(strName, ",")(1))
strBody = "<Font Face=calibri>Dear " & strName2 & ", <br><br> Please approve the following divisions:<br><br>"
With OutMail
.To = rng.Cells(r, 2).Value
.Subject = "Please Approve Divisions"
List = strName3 & "<br>"
Do While rng.Cells(r, 1).Value = rng.Cells(r + 1, 1)
r = r + 1
Set strDept = rng.Cells(r, 3)
.Subject = "Approvals Needed!"
List = .HTMLBody & strDept & "<br>"
r = r + 1
.HTMLBody = List
Loop
.HTMLBody = strBody & "<B>" & List & "</B>" & "<br>" & Signature
.Display
End With
Set OutMail = Nothing
r = r + 1
Loop
Set OutApp = Nothing
End Sub
最佳答案
我删除了之前的答案,然后取消删除它,以防您需要该信息。为了不让任何人感到困惑,从 OP 代码构建的答案如下。
免责声明:我不喜欢 Do While 中递增的代码风格,这使得追踪错误变得非常困难,但我理解其意图。我已经按照我的大脑工作方式以及也许更好的编码风格在下面包含了代码,您来评判。
好吧@learningthisstuff我弄清楚发生了什么,代码假设名称已排序。没有提供的一件事是,如果部门名称相同,它将被列出多次,如果存在不同代码的欺骗,该部门对于一个人来说总是唯一的(没有欺骗?)。
这段代码有效,我只是将它作为虚拟集上的宏运行。最重要的是排序和递增逻辑,我改变了一些东西以使其更具可读性/易于理解。
我希望这对您有所帮助,并且您可以根据情况的变化进行修改。
Sub Email_Macro()
'
' Email_Macro Macro
'
Dim OutApp As Object
Dim OutMail As Object
Dim cell, lookrng As Range
Dim strdir As String
Dim strFilename As String
Dim sigString As String
Dim strBody As String
Dim strName As Variant
Dim strName1 As Variant
Dim strDept As Variant
Dim strName2 As String
Dim strbody2 As String
Dim strName3 As Variant
Dim emailWS As Worksheet
Dim nameCol As Double
Dim deptCol As Double
Dim lastRow As Double
Dim startRow As Double
Dim r As Double
Dim depList As String
deptList = ""
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set emailWS = ThisWorkbook.ActiveSheet
startRow = 2 ' starting row
nameCol = 1 'col of name
deptCol = 3 'col of dept
'find the last row with a name in it from the name column
lastRow = emailWS.Cells(emailWS.Rows.Count, nameCol).End(xlUp).Row
'set variable to the starting row #
r = startRow 'this is where the counting begins
'sort the data first before going through the email process
'assumes these are the only columns 1 (nameCol) thru 3 (deptCol) to sort
'assumes you are sorting based on col 1 (nameCol)
emailWS.Range(Cells(startRow, nameCol), Cells(lastRow, deptCol)).Sort key1:=emailWS.Range(Cells(startRow, nameCol), Cells(lastRow, nameCol))
Do While r <= lastRow
Set OutMail = OutApp.CreateItem(0)
Set strName = emailWS.Cells(r, nameCol)
Set strName3 = emailWS.Cells(r, deptCol)
'careful the line below assumes there is always a comma separator in the name
strName2 = Trim(Split(strName, ",")(1))
strBody = "<Font Face=calibri>Dear " & strName2 & ", <br><br> Please approve the following divisions:<br><br>"
With OutMail
.To = emailWS.Cells(r, 2).Value
.Subject = "Please Approve Divisions"
deptList = strName3 & "<br>"
Do While emailWS.Cells(r, 1).Value = emailWS.Cells(r + 1, 1)
r = r + 1
Set strDept = emailWS.Cells(r, 3)
.Subject = "Approvals Needed!"
deptList = deptList & strDept & "<br>"
Loop
.HTMLBody = strBody & "<B>" & deptList & "</B>" & "<br>" & Signature
.Display
End With
Set OutMail = Nothing
'conditionally increment the row based on the name difference
If emailWS.Cells(r, 1).Value <> emailWS.Cells(r + 1, 1) Then
r = r + 1 'increment if there is a new name or no name
deptList = "" 'reset the department list
Else 'Do nothing
End If
Loop
Set OutApp = Nothing
End Sub
屏幕截图:
为了证明我不会在没有一些解决方案/指导的情况下发表评论?这对我来说更容易理解和排除故障。它以非常可预测的方式逐步遍历行,并且我们根据指定的条件处理每一行。我还尝试使用变量名称,让您知道它们的用途。
Sub Email_Macro()
'
' Email_Macro Macro
'
Dim OutApp As Object 'email application
Dim OutMail As Object 'email object
Dim strBody As String 'first line of email body
Dim strName As String 'name in the cell we are processing
Dim strDept As String 'dept of the name we are processing
Dim previousName As String 'previous name processed
Dim nextName As String 'next name to process
Dim emailWS As Worksheet 'the worksheet selected wehn running macro
Dim nameCol As Double 'column # of names
Dim deptCol As Double 'column # of depts
Dim lastRow As Double 'last row of data in column
Dim startRow As Double 'row we wish to start processing on
Dim r As Double 'loop variable for row
'This will be the list of departments, we will build it as we go
Dim depList As String
Dim strSig As String 'email signature
strSig = "Respectfully, <br> Wookie"
deptList = "" 'empty intitialization
previousName = "" 'empty intialization
nextName = "" 'empty intialization
'Turn off screen updating
'Application.ScreenUpdating = False
'choose email application
Set OutApp = CreateObject("Outlook.Application")
'set worksheet to work on as active (selected sheet)
Set emailWS = ThisWorkbook.ActiveSheet
startRow = 2 ' starting row
nameCol = 1 'col of names, can also do nameCol = emailWS.Range("A1").Column
deptCol = 3 'col of depts, can also do deptCol = emailWS.Range("A3").Column
'** Advantage of the optional way is if you have many columns and you don't want to count them
'find the last row with a name in it from the name column
lastRow = emailWS.Cells(emailWS.Rows.Count, nameCol).End(xlUp).Row
'sort the data first before going through the email process using Range sort and a key
'assumes these are the only columns 1 (nameCol) thru 3 (deptCol) to sort
'assumes you are sorting based on col 1 (nameCol)
emailWS.Range(Cells(startRow, nameCol), Cells(lastRow, deptCol)).Sort key1:=emailWS.Range(Cells(startRow, nameCol), Cells(lastRow, nameCol))
'Set up our loop, it will go through every cell in the column we select in the loop
For r = startRow To lastRow
'Get the name and dept
'For the name we will split around the comma and take the second part of array (right of comma)
strName = Trim(Split(emailWS.Cells(r, nameCol), ",")(1))
strDept = emailWS.Cells(r, deptCol)
'if the next name is not blank (EOF)
If emailWS.Cells(r + 1, nameCol) <> "" Then
'assign the next name
nextName = Trim(Split(emailWS.Cells(r + 1, nameCol), ",")(1))
Else
'this is your EOF exit so assume a name
nextName = "Exit"
End If 'Else do noting on this If
If strName <> previousName Then
'Set our "new" name to previousName for looping
'process the "new" name
previousName = strName
'create the email object
Set OutMail = OutApp.CreateItem(0)
'Process as new email
With OutMail
.To = strName 'address email to the name
.Subject = "Please Approve Divisions" 'appropriate subject
deptList = strDept & "<br>" 'add the dept to dept list
'Build the first line of email body in HTML format
strBody = "<Font Face=calibri>Dear " & strName & ", <br><br> Please approve the following divisions:<br><br>"
End With
Else
'The name is the same as the email we opened
'Process Dept only by adding it to string with a line break
deptList = deptList & strDept & "<br>"
End If
'Do we send the email and get ready for another?
If strName <> nextName Then
'the next name is not the same as the one we are processing and we sorted first
'so it is time to send the email
OutMail.HTMLBody = strBody & "<B>" & deptList & "</B>" & "<br><br>" & strSig
OutMail.Display
Else 'Do Nohing
End If
Next r 'move to the next row
'nullify email reference
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
如果你想防止重复的部门,那么我会这样做,你可以看到它的去向,只有一个结尾:
End With
Else
'The name is the same as the email we opened
'Process Dept only by adding it to string with a line break
If InStr(deptList, strDept) = 0 Then
'Dept is not in the list so Add the department
deptList = deptList & strDept & "<br>"
Else
'Do nothing, the dept is already there
End If
End If
我想永远不要放弃。一切皆有可能,也许只是超出了我们当前的技能范围(所以寻求一些帮助并继续学习)。
干杯 - WWC
关于excel - 从 Excel Loop 创建电子邮件,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47946041/
在电子邮件中 Received: header 可以合法地多次出现,并且具有互斥的值... Received: three.example.com Received: two.example.co
是否有任何代码/宏可以合并到我的 sas 程序中,一旦我的 sas 代码在运行时发生错误,它会立即给我发送电子邮件? 另外,这封电子邮件是否可能包含发生的错误? 最佳答案 是的……也不是…… 这是可能
我有一个包含三个 td 的表格,每个表格都需要包含图像。 td 的宽度和高度是固定的,但图像大小可以变化。目标是在不扭曲单元格或图像本身的情况下拟合图像。不能使用 background-image 属
首先非常感谢大家过去提出的宝贵建议,我们正在创建一个应用程序,在某些事件中想要将电子邮件/短信发送到我们已经尝试过 openURL 调用的指定电话号码,但它会打开现有的内置iPhone 的电子邮件/短
我正在使用 apache commons mail 发送电子邮件。不幸的是,我遇到了以下异常: org.apache.commons.mail.EmailException: Sending the
我可以在我的 ~/.hgrc 文件中设置我常用的电子邮件地址,但是有没有办法为一个 hg 项目指定我想被称为不同的名称/电子邮件(类似到项目目录中的 git 的 .git/config 文件覆盖 ~/
$message = 'New user registration\n\n There is a new submission on the site and below are the detail
使用 outlook 我可以发送在邮件正文中插入图像的电子邮件(不是作为附件)。我如何使用 PHP 中的 mail() 函数来做到这一点? 最佳答案 我会推荐 Swift Mailer: http:/
以下代码的目标是将所选图表粘贴到我的文本下方的电子邮件正文中。但是,它继续将其粘贴在我的文本上方。我该如何更改它以使其粘贴在下面?谢谢! Set OutApp = CreateObject("Outl
首先,我知道不建议使用正则表达式发送电子邮件,但我必须对此进行测试。 我有这个正则表达式: \b[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b 在 Java 中,我这样
如何在没有任何第三方程序的情况下从 Python 发送电子邮件? 最佳答案 使用Python email和 smtplib模块。示例:http://docs.python.org/library/em
我目前正在使用此代码在 html 表中显示 mysql 记录 "; . . echo ' '. $row["Email1"] . ' '; . . echo ""; }
在电子邮件中使用 HTML 时,是否可以仅将链接的一部分着色为特定颜色? 我试过: red part of link normal part ...我知道如果我拆分链接是可能的,但我正在努力将它们保持
我正在处理一封 html 电子邮件,我有一个非常简单的元素 (ul),我想将它移到页面下方。 我检查了campaign monitor's guide并且不支持负边距,或者 position: abs
我使用表格创建了我的 HTML 电子邮件,该表格有一个背景图像,在大多数基于 Web 的电子邮件客户端中都能正常显示。 我正在努力让背景图片显示在 Outlook 中。 我最近的尝试,我尝试了以下操作
我对 PHP/CSS 和一般编程还很陌生。 我想改变文本区域中文本的格式,就像在这里所做的那样,例如,当为突出显示的文本添加标签“代码示例”时,它会缩进它,或者当将它设置为粗体时,它会加粗它。 这样做
嘿,你能推荐我哪些 C++ 库或类可用于在 C++ 中通过 SMTP 发送电子邮件。我在 Windows 平台上。我需要一个支持附件和 SSL 连接的库。有哪些可用选项。我不打算实现我自己的 :) 问
想知道是否可以在 HTML 电子邮件中包含一个表单。我要做的就是将图像输入提交到 Paypal 购买页面。我希望它直接进入 Paypal ,而无需先进入营销页面... 我会拥有 paypal 要求的完
我负责“ reshape ”我们的 IT 部门通信。我想用纯 HTML/CSS 来发送我们的电子邮件通知,以确保它的可移植性。 下面是代码,它在 Outlook 中看起来完全符合我的要求,但是一旦将内
我正在学习编写响应式电子邮件模板。目前我有:https://jsfiddle.net/q12yg2z6/
我是一名优秀的程序员,十分优秀!