[VBA] Some Scripting Help in Excel?

[Nick H. Supervisor]

Hi guys,

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.

Cheers!

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.

[bane7378]

  On 24/04/2013 at 14:00, Intrinsica said:

Hi guys,

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.

Cheers!

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.

The script works fine when I try it here using Excel 2010 and Outlook 2010. It sends the email and the email shows up in my external email account. Are you saving the workbook as an xlsx or xls? When I initially tried saving as an xlsx it warned me that the script wouldn't work in a macro-free workbook and that I would need to save it with a file type that would support the script so I saved it as an xls. I need to look into why it is throwing that error with xlsx since I know I have used macros in xlsx workbooks before but that was the only issue I ran into.

[Nick H. Supervisor]

Huh, how odd. So I wonder why it isn't working here then...maybe there's a security policy in place that prevents the email being sent?

I tried saving it as an .xlsx, .xls and .xlsm, none of them seemed to help sending the email out.

I guess I'll stick with it for a bit longer and see if I can make some progress.

[Haggis Veteran]

did u ever get this working?

you should get a popup and it should ask you for permission to allow emails to be sent on your behalf

and yeah works perfectly well for me too :)