• 0

[VBA] Some Scripting Help in Excel?


Question

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.

Link to comment
https://www.neowin.net/forum/topic/1148764-vba-some-scripting-help-in-excel/
Share on other sites

3 answers to this question

Recommended Posts

  • 0
  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.

  • 0

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.

This topic is now closed to further replies.
  • Posts

    • TP-Link Tri-Band AXE5400 Wi-Fi 6E Gaming Router Archer GXE75 is just $135 by Sayan Sen If you’re juggling gaming consoles, 4K streams and a growing roster of smart devices, the TP-Link Archer GXE75 Tri-Band AXE5400 Wi-Fi 6E Gaming Router is a good option to look at right now, considering the device is currently on sale at just $135 (purchase link under the specs table below). The Archer GXE75 promises up to 5.4 Gbps of throughput across three bands: 574 Mbps on 2.4 GHz, 2402 Mbps on 5 GHz and 2402 Mbps on 6 GHz (HE160 channels enabled). A 2.5 Gbps WAN/LAN port pairs with three 1 Gbps LAN ports and a USB 3.0 port for fast file sharing or printer access (purchase link down below). The quad-core CPU and 512 MB of RAM should help to handle multiple streams without bogging down, while MU-MIMO, OFDMA and 1024-QAM improve efficiency when several devices connect simultaneously. The router’s built-in Game Accelerator engine is said to automatically prioritize gaming packets (both wired and wireless), and TP-Link’s GPN (Gamers Private Network) acceleration can reduce packet loss for supported titles. HomeShield security brings firewall protection, device quarantining and parental controls. The technical specifications of the router are given below: Specification Details Wireless Standards IEEE 802.11ax 6 GHz; IEEE 802.11ax/ac/n/a 5 GHz; IEEE 802.11ax/n/g/b 2.4 GHz Wi-Fi Speeds 6 GHz: 2402 Mbps (802.11ax); 5 GHz: 2402 Mbps (802.11ax); 2.4 GHz: 574 Mbps (802.11ax) Spatial Streams 6 streams (tri-band OFDMA/MU-MIMO) Processor 1.7 GHz 64-bit Quad-Core CPU Memory 512 MB high-speed RAM Ethernet Ports 1× 2.5 Gbps WAN/LAN; 1× 1 Gbps WAN/LAN; 3× 1 Gbps LAN USB 1× USB 3.0 SuperSpeed port (up to 10× faster than USB 2.0) Antennas 4× high-performance external antennas with Beamforming Buttons Power On/Off; Reset; WPS/Wi-Fi; LED On/Off Working Modes Router Mode; Access Point Mode Security WPA, WPA2, WPA3, WPA/WPA2-Enterprise; SPI firewall; Access Control; IP/MAC binding; Application-layer gateway; HomeShield security suite VPN OpenVPN, PPTP, L2TP (server & client); WireGuard (server & client) Software & Services IPv4/IPv6; TP-Link HomeShield; EasyMesh; Parental controls; QoS by device; WAN types (Dynamic IP, Static IP, PPPoE, PPTP, L2TP); DDNS (TP-Link, NO-IP, DynDNS); auto firmware updates Gaming Features Game QoS boost; gaming-port priority; Gamers Private Network acceleration; game-port forwarding; real-time game panel with stats & RGB control Operating Environment 0 °C – 40 °C; 10 %– 90 % non-condensing humidity Get the TP-Link Tri-Band AXE5400 Wi-Fi 6E Gaming Router Archer GXE75 at the link below: TP-Link Tri-Band AXE5400 Wi-Fi 6E Gaming Router Archer GXE75 | EasyMesh, HomeShield: $159.99 + $25 off with coupon => $134.99 (Sold and Shipped by Amazon US This Amazon deal is US-specific and not available in other regions unless specified. If you don't like it or want to look at more options, check out the Amazon US deals page here. Get Prime (SNAP), Prime Video, Audible Plus or Kindle / Music Unlimited. Free for 30 days. As an Amazon Associate, we earn from qualifying purchases.
    • Oh no... here we go again. Tha same sh*t that happaned to Mail & Calendar...
    • I too have worked with pro gear for decades and so I found the interface to be unintuitive from both the pro and casual side (a rare "accomplishment" by what are obviously otherwise skilled coders, hehe). I eventually got it to work, thanks for offering, but I found other compatibility issues with my own use case, so I just dropped it entirely. Right now, I just use an analog line out/line in approach which works as expected across all usage scenarios. With both machines on the same power block/outlet, I'm not getting any analog hum or hiss. My next step will be to try the updated Multiplicity 4 when a bug, addressing this very issue unfortunately, is resolved. Knowing Stardock, that could be tomorrow or five years from now, so I check back every few months to see it it's fixed and I want to upgrade. Again, thanks for offering to help.
    • We recognize that performance can use some improvements, and we continue to work on improving it. However, it's worth noting that massive performance improvements don't happen overnight, they take a lot of work and effort, and in most cases, the improvements are more noticeable when you compare across several updates. That said, Files is open-source and everyone is invited to help with these efforts 🙂
  • Recent Achievements

    • Rookie
      Snake Doc went up a rank
      Rookie
    • First Post
      nobody9 earned a badge
      First Post
    • One Month Later
      Ricky Chan earned a badge
      One Month Later
    • First Post
      leoniDAM earned a badge
      First Post
    • Reacting Well
      Ian_ earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      495
    2. 2
      Michael Scrip
      203
    3. 3
      ATLien_0
      197
    4. 4
      Xenon
      137
    5. 5
      +FloatingFatMan
      115
  • Tell a friend

    Love Neowin? Tell a friend!