使用宏代码自动发送电子邮件 [英] Automatically send an email using macros code

问题描述

Hello

Hello

我正在尝试在excel上编写一个宏代码，用于查看个人检查部分输入的截止日期然后向他们发送一封电子邮件，提醒他们该部分到期。我在同一个excel
表单上也有他们的电子邮件列。除了部件号，截止日期，名称和发送给它的电子邮件之外，所有电子邮件都具有相同的正文消息。我很难让宏在它找到日期的同一行中使用电子邮件。这是我到目前为止：

I'm trying to write a macros code on excel that will view due dates that have been entered by individuals checking parts out and then send them an email reminding them that the part is due. I have a column also for their emails on the same excel sheet. The emails all have the same body msg except for the part number, the due date, the name, and the email its being sent to. I'm struggling to get macros to use the email in the same row that it has found the date on. This is what I have so far:

子电子邮件（）



    Dim r As Range

    Dim cell作为范围



   设置r =范围（"G2：G64"）



   对于每个单元格r



       如果cell.Value = Date那么



            Dim Email_Subject，Email_Send_From，Email_Send_To，_

            Email_Cc，Email_Bcc，Email_Body As String

            Dim Mail_Object，Mail_Single As Variant



            Email_Subject =" Gauge Return Date"

            Email_Send_From =" *** @ * ob.com"

            Email_Send_To = Cells（ActiveCell.Row，2）

            Email_Body ="亲爱的&安培;细胞（ActiveCell.Row，1）& "，" &安培; vbCrLf& vbCrLf& "下面的仪表是由于" &安培;细胞（ActiveCell.Row，7）& "：" &安培; vbCrLf
& vbCrLf& "Gauge Number：" &安培;细胞（ActiveCell.Row，5）& vbCrLf& vbCrLf& "请务必退回仪表或重新检查。如果重新检查仪表，请关闭当前的结账并标记为：返回;
然后开始新的。" &安培; vbCrLf& vbCrLf& "谢谢，" &安培; vbCrLf& vbCrLf& "Gauge Lab"



            On Error GoTo调试

           设置Mail_Object = CreateObject（" Outlook.Application"）

           设置Mail_Single = Mail_Object.CreateItem（0）

           使用Mail_Single

            .Subject = Email_Subject

            .To = Email_Send_To

            .cc = Email_Cc

            .BCC = Email_Bcc

            .Body = Email_Body

            .send

           结束与



       结束如果



   下一页





   退出Sub



调试：

  &NBSP; &NBSP; &NBSP;如果Err.Description<> ""然后MsgBox Err.Description

End Sub

Sub email()

    Dim r As Range
    Dim cell As Range

    Set r = Range("G2:G64")

    For Each cell In r

        If cell.Value = Date Then

            Dim Email_Subject, Email_Send_From, Email_Send_To, _
            Email_Cc, Email_Bcc, Email_Body As String
            Dim Mail_Object, Mail_Single As Variant

            Email_Subject = "Gauge Return Date"
            Email_Send_From = "***@*ob.com"
            Email_Send_To = Cells(ActiveCell.Row, 2)
            Email_Body = "Dear " & Cells(ActiveCell.Row, 1) & "," & vbCrLf & vbCrLf & "The gauge below was due by " & Cells(ActiveCell.Row, 7) & ":" & vbCrLf & vbCrLf & "Gauge Number: " & Cells(ActiveCell.Row, 5) & vbCrLf & vbCrLf & " Please be sure to return the gauge or re-check it out. If re-checking out a gauge, please close out the current check out and mark as: returned; then begin a new one." & vbCrLf & vbCrLf & "Thanks," & vbCrLf & vbCrLf & "Gauge Lab"

            On Error GoTo debugs
            Set Mail_Object = CreateObject("Outlook.Application")
            Set Mail_Single = Mail_Object.CreateItem(0)
            With Mail_Single
            .Subject = Email_Subject
            .To = Email_Send_To
            .cc = Email_Cc
            .BCC = Email_Bcc
            .Body = Email_Body
            .send
            End With

        End If

    Next


    Exit Sub

debugs:
        If Err.Description <> "" Then MsgBox Err.Description
End Sub

推荐答案

你好AhmadShehata

Hi AhmadShehata

我没有时间测试你的代码，但我认为你的问题在于：

I have not had time to test your code, but I think that you problem lies in the line:

" Email_Send_To = Cells（ ActiveCell.Row，2）"

"Email_Send_To = Cells(ActiveCell.Row, 2)"

它不是"ActiveCell"。你在看，但是"细胞"是指细胞。在你的For Next循环中。

It is not the "ActiveCell" that you are looking at, but the "cell" in your For Next loop.

尝试用以下代码替换该行：

Try replacing that line with:

Email_Send_To = Cells（cell.Row，2）

Email_Send_To = Cells(cell.Row, 2)

Andy C

这篇关于使用宏代码自动发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！