Jump to content

Question

Posted

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.

[b]A[/b] - Name of user
[b]B[/b] - email address of user
[b]C[/b] - A value of "yes" or "no"
[b]D[/b] - 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 [url="http://www.rondebruin.nl/win/s1/outlook/bmail5.htm"]here[/url] which may also include a better explanation than I have):

[code]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[/code]

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

3 answers to this question

  • 0

Posted

[quote name='Intrinsica' timestamp='1366812022' post='595655710']
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.

[b]A[/b] - Name of user
[b]B[/b] - email address of user
[b]C[/b] - A value of "yes" or "no"
[b]D[/b] - 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 [url="http://www.rondebruin.nl/win/s1/outlook/bmail5.htm"]here[/url] which may also include a better explanation than I have):

[code]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[/code]

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.
[/quote]

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

Posted

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

Posted

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

[attachment=333372:allow.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.