英文:
Sending Excel row in Outlook email
问题
我的Excel表格包含任务名称、分配的人员、分配的人员电子邮件地址和截止日期。
每个月的第一天，我想要检查表格，并在任务过期时通过Outlook自动发送电子邮件。
我只想发送他们的任务。
我找到的代码会共享整个表格，而不是特定的行。
英文:
My Excel sheet contains the task name, assigned person, assigned person email address, due date.
On the first day of every month I want to check the sheet and send emails automatically through Outlook when the task is overdue.
I want to send only their task.
The codes I found will share the whole sheet not specific rows.
答案1
得分: 1
示例：
存储在第2行开始的值
A列 - 任务名称
B列 - 人员名称
C列 - 电子邮件
D列 - 截止日期
Sub CheckOverdue()
Dim sht As Worksheet
Dim xcell As Range
Dim lastRow As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
' 最后一个带有截止日期的D列中的行
lastRow = sht.Cells(Rows.Count, "D").End(xlUp).Row
' 遍历D列中的每个截止日期
For Each xcell In sht.Range("D2:D" & lastRow)
If Not IsDate(xcell) Then
MsgBox "截止日期在 " & xcell.Address(False, False) & " 处的值错误"
Exit Sub
End If
If Date > CDate(xcell) Then
With CreateObject("outlook.application").CreateItem(0)
.To = sht.Range("C" & xcell.Row)
.Subject = "已逾期"
.Body = "你好，" & sht.Range("B" & xcell.Row) & vbNewLine & "你的任务 " & sht.Range("A" & xcell.Row) & " 已逾期"
.Display ' 在测试后更改为 .Send 以自动发送电子邮件
End With
End If
Next xcell
End Sub
英文:
example:
values stored starting at row 2
column A - task name
column B - person name
column C - email
column D - due date
Sub CheckOverdue()
Dim sht As Worksheet
Dim xcell As Range
Dim lastRow As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
'last row with value in column D with due dates
lastRow = sht.Cells(Rows.Count, "D").End(xlUp).Row
'loop thru each due date in column D
For Each xcell In sht.Range("D2:D" & lastRow)
If Not IsDate(xcell) Then
MsgBox "Error in value of due date in " & xcell.Address(False, False)
Exit Sub
End If
If Date > CDate(xcell) Then
With CreateObject("outlook.application").createitem(0)
.To = sht.Range("C" & xcell.Row)
.Subject = "Overdue"
.Body = "Hi, " & sht.Range("B" & xcell.Row) & vbNewLine & "Your task " & sht.Range("A" & xcell.Row) & " is overdue"
.Display ' change to .Send after testing to automatically send the email
End With
End If
Next xcell
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论