如何使用Excel VBA发送电子邮件 [英] How to send email using excel VBA

问题描述

我的A到H列中包含数据.A列中的数据是电子邮件地址.我在另一个单元格中放置了一个复选框.我想要的是，当我勾选复选框时，它将继续发送电子邮件到位于最后一行A列中的电子邮件帐户.但是下面的代码仅适用于单元格A1.

I have data contained in column A to H. The data in column A are email addresses. I put a check box in another cell. What I want is when I tick the check box it proceeds to send email to the email account placed in last row column A. But the below code only works for cell A1.

Private Sub CheckBox1_Click()

    Dim Email As String

    Row = 1
    Email = Sheet1.Cells(Row, 1)
    Do Until Sheet1.Cells(Row, 1) = ""
        Row = Row + 1
    Loop

    Dim OutApp As Object, OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = Email
        .Subject = score
        .HTMLBody = "This is a contain of Email Message"
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing

End Sub

推荐答案

在下面的部分中，您尝试在其中获取最后一行，然后从该单元格中获取电子邮件:

In your section below, where you are trying to get the last row and then the email from that cell:

Row = 1
Email = Sheet1.Cells(Row, 1)
Do Until Sheet1.Cells(Row, 1) = ""
    Row = Row + 1
Loop

您要从第一行中提取 Email ，然后在 Do Before 循环中，您要检查最后一行，但不进行修改 Email 变量.

you are taking the Email from the first row, and after in the Do Until loop you are checking for the last row, but not modifying the Email variable.

解决:无需循环即可在A列中找到具有有效电子邮件地址的最后一行，只需在下面的几行中找到它即可:

Resolve: There is no need to have a loop to find the last row with a valid email address in Column A, you can simply find it with the following lines below:

Dim LastRow As Long

' get last row with data in Column A (don't skip blank cells in the middle)
LastRow = Sheet1.Range("A1").End(xlDown).Row
Email = Sheet1.Range("A" & LastRow).Value

其余代码工作正常.

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