Sign in to follow this  
Followers 0
Nick H.

[VBA] Some Scripting Help in Excel?

4 posts in this topic

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

post-229387-0-37747300-1368458336.jpg

and yeah works perfectly well for me too :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.