VBA Excel - 如果一列中的单元格包含电子邮件地址，并且另一列中的单元格是“SOLD” ， 发电子邮件 [英] VBA Excel - If cell in one column contains email address and cell in another column is "SOLD" , send email

问题描述

今天开始尝试使用VBA。创建Excel工作表以跟踪SOLD，PENDING，LOST，这将允许销售人员单击按钮一次将组电子邮件发送到一个类别。经过多次搜索，我发现一些代码通过检查一列以确保正确的地址在那里发送组电子邮件。我发现一些其他代码，我以为会检查作业状态列，以便只有SOLD或任何将被选择为电子邮件。我是一个无知的初学者，需要帮助。这是正在工作的代码，直到我添加了 - 如果Sh.Cells（Target.Row，7）=PENDING然后 - 部分。任何帮助将不胜感激。谢谢！

  Private Sub CommandButton1_Click（）
 Dim cell As Range 
 Dim strto As String 
对于每个单元格在ThisWorkbook.Sheets（Sales 2013​​）。范围（E3：E500）
如果cell.Value像?*@?*.?*然后
如果Sh。单元格（Target.Row，7）=PENDING然后
 strto = strto& cell.Value& ; 
 End If 
 End If 
下一个单元格
如果Len（strto）> 0然后strto = Left（strto，Len（strto） -  1）
 
 Application.ScreenUpdating = False 
设置OutApp = CreateObject（Outlook.Application）
 OutApp.Session .Logon 
 
错误GoTo清理
设置OutMail = OutApp.CreateItem（0）
错误恢复下一步
使用OutMail 
 .To =Anchor销售
 .Bcc = strto 
 .Subject =在这里输入主题
 .Body ='现在可以
'用于输入身体文字中的收件人名称here
'亲爱的&单元格（cell.Row，A）。值_ 
& vbNewLine& vbNewLine& _ 
输入正文& _ 
这里
'你可以添加这样的
'.Attachments.Add（C：\test.txt）
'.Send'或使用显示
。显示
结束
错误GoTo 0 
设置OutMail =没有
清理：
设置OutApp =没有
 Application.ScreenUpdating = True 
 End Sub 
 
 Private Sub CommandButton2_Click（）
 
 End Sub 
  

请帮助！

解决方案

当然，我宁愿每个人发一封邮件如果收件人不知道对方），但是让我们留在你的问题。

你只需要微小的修改：

私人子命令Button1_Click（）
Dim cell As Range
Dim strto As String
对于每个单元格在ThisWorkbook.Sheets（Sales 2013​​）中.Range（E3：E500）
如果cell.Value =PENDING，则strto = strto& cell.offset（0，1）.value& ;

我不知道你是否需要如果cell.Value Like？ @ ？。* *或者是从您发现的代码复制粘贴...如果需要，最后一行必须由

 如果cell.Value像?*@?*.?*和cell.Value =PENDING然后strto = strto& cell.offset（0，1）.value& ; 
 
'in Offset（0,1）我假设mailadress位于待定单元格旁边的单元格
下一单元格
如果Len（strto）> 0然后strto = Left（strto，Len（strto） -  1）
  

其余的，因为你有

问题是由

 如果Sh.Cells Target.Row，7）=PENDING然后
  

因为你没有sh的定义 - 但是你也不需要它。

我希望这有助于

Just started experimenting with VBA today. Creating Excel sheet to track SOLD, PENDING, LOST that will allow salesmen to click a button to send a group email to one category at a time. After much searching I found some code that works well to send group email by checking a column to make sure a proper address is there. I found some other code that I thought would check the "Job Status" column so that only the "SOLD" or whatever would be chosen for email. I am a clueless beginner and need help. Here is the code that was working until I added the - If Sh.Cells(Target.Row, 7) = "PENDING" Then - part. Any help would be greatly appreciated. Thanks!

Private Sub CommandButton1_Click()
Dim cell As Range
 Dim strto As String
 For Each cell In ThisWorkbook.Sheets("Sales 2013").Range("E3:E500")
 If cell.Value Like "?*@?*.?*" Then
    If Sh.Cells(Target.Row, 7) = "PENDING" Then
     strto = strto & cell.Value & ";"
    End If
 End If
 Next cell
 If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

Application.ScreenUpdating = False
 Set OutApp = CreateObject("Outlook.Application")
 OutApp.Session.Logon

 On Error GoTo cleanup
 Set OutMail = OutApp.CreateItem(0)
 On Error Resume Next
 With OutMail
 .To = "Anchor Sales"
 .Bcc = strto
 .Subject = "Enter subject here"
 .Body = "" ' EMPTY FOR NOW
 'USE THIS FOR ENTERING NAMES OF RECIPIENTS IN BODY TEXT "here"
 '"Dear" & Cells(cell.Row, "A").Value _
 & vbNewLine & vbNewLine & _
 "Enter body text " & _
 "here"
 'You can add files also like this
 '.Attachments.Add ("C:\test.txt")
 '.Send 'Or use Display
 .Display
 End With
 On Error GoTo 0
 Set OutMail = Nothing
cleanup:
 Set OutApp = Nothing
 Application.ScreenUpdating = True
End Sub

Private Sub CommandButton2_Click()

End Sub

Please help!

解决方案

of course I would prefer to send one mail per Person (if the recipients shall not know each other), but let's stay with your Problem.

you only need minor changes:

Private Sub CommandButton1_Click()
Dim cell As Range
 Dim strto As String
 For Each cell In ThisWorkbook.Sheets("Sales 2013").Range("E3:E500")
 If cell.Value = "PENDING" Then strto = strto & cell.offset(0, 1).value & ";" 

I am not sure if you Need the part "If cell.Value Like "?@?.?*" " or if that is from copy-paste from the code you found... If you Need it, the last line would have to be replaced by

 If cell.Value Like "?*@?*.?*" and cell.Value = "PENDING" Then strto = strto & cell.offset(0, 1).value & ";"  

    'in Offset(0,1) I assume the mailadress is in the cell next to the cell tested for "pending" 
 Next cell
 If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

the rest as you have it.

the Problem was caused by

    If Sh.Cells(Target.Row, 7) = "PENDING" Then

as you have no definition for "sh" - but you also don't need it.

I hope this helps

这篇关于VBA Excel - 如果一列中的单元格包含电子邮件地址，并且另一列中的单元格是“SOLD” ， 发电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！