I've got a problem with a VBA script that someone created for Excel. This script is supposed to deal with four columns.
A - Name of user
B - email address of user
C - A value of "yes" or "no"
D - A column filled by the script (more on that in a second)
Here is the idea: When the macro is run, it will look at column C. If column C says, "Yes" then it will send an email to the address in Column B, automatically including the user's name (column A) at the top of the email. Once the email has been sent to the address, the script will fill column D with "send" so that when the script is run in future it knows not to send another message to that user.
Here is the script (taken from here which may also include a better explanation than I have):
Sub Test2() 'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm 'Working in Office 2000-2013 Dim OutApp As Object Dim OutMail As Object Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") On Error GoTo cleanup For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants) If cell.Value Like "?*@?*.?*" And _ LCase(Cells(cell.Row, "C").Value) = "yes" _ And LCase(Cells(cell.Row, "D").Value) <> "send" Then Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = cell.Value .Subject = "Reminder" .Body = "Dear " & Cells(cell.Row, "A").Value _ & vbNewLine & vbNewLine & _ "Please contact us to discuss bringing " & _ "your account up to date." 'You can add files also like this '.Attachments.Add ("C:\test.txt") .Send 'Or use Display End With On Error GoTo 0 Cells(cell.Row, "D").Value = "send" Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub
My VBA coding ability isn't worth a penny, but from what I can see in that script it seems to make sense. Sure enough, when I have run a test the D column has populated with "send." But I do not receive an email.
This script is used in Excel 2010, trying to communicate with Outlook 2010 (both programs were open at the same time).
Can someone see an issue with the above script that I've missed? Or can someone think of an easier way of getting this kind of thing to work?
If anyone needs more information, let me know.
EDIT: I should have probably mentioned that I will be modifying the script at some stage so that it actually fits in with the exact task I'm trying to do, but for the moment I just want the "basic" script to work.
Also, if anyone has any tips for quick-learning VBA it would be appreciated.