Link to home
Start Free TrialLog in
Avatar of Sharmal Butler
Sharmal ButlerFlag for United States of America

asked on

Adjust VBA email notification script

Experts,

1.   I am looking to have the notification email go to a group email box  instead of going to the individual Business Unit Contact (BUC)  The reason for the change is to accommodate any BUC staff changes.

2.  In conjunction with the notification being redirected to a group email box the embedded table columns need adjusted to read as the following:  Ref#, Quote#, Team Name, Start date, End Date, Term,  Mo.Total, Qty, Total and BUC name.  By doing it this way eliminates the need for multiple emails and allows for all Quotes expiring within 40 days to be listed in table.


https://filedb.experts-exchange.com/incoming/2019/12_w51/1439298/29166782c.xlsm
https://filedb.experts-exchange.com/incoming/2019/12_w51/1439310/email-sample.docx
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Is "Mo.Total" what is in column Original Quotes column 'N'?
Is "BUC name" what is in column Original Quotes column 'R'?

I don't understand this.
By doing it this way eliminates the need for multiple emails and allows for all Quotes expiring within 40 days to be listed in table.
Currently the emails are grouped by the List sheet Business Unit Name. What would change?
Avatar of Sharmal Butler

ASKER

Is "Mo.Total" what is in column Original Quotes column 'N'?

"Mo Total"  s/b called "Monthly price"  in col L on the Original Quote Sheet

Is "BUC name" what is in column Original Quotes column 'R'?

Yes, Col R on the Original Quotes sheet

Currently the emails are grouped by the List sheet Business Unit Name. What would change?

The primary change would be that in the To: all BUCs on the list sheet as well as the  managers on the Management sheet will go into one email address vice different individual email address.  Therefore, even if a BUCs quote is not up for renewal they will still be on the "To:" thread to receive the email. For example:

BUCs -
Amanda White -  Salesforce@gmail.com
Paul Smith -  Salesforce@gmail.com

Management -
Cathy Pawlowsky -  Salesforce@gmail.com
Sharmal Butler -  Salesforce@gmail.com

Salesforce@gmail.com is the group box that all the folks will be added to. Please note that Salesforce@gmail.com is not the actual group email box.  For testing purposes everyone can be sent to my email address.  I hope this makes sense.
For the email address there are currently two places where they are found: 'List' sheet column 'G' which has multiple entries, and 'Management' sheet column 'C' which one entry per manager. Since you say "all BUCs on the list sheet as well as the  managers on the Management sheet will go into one email address vice different individual email address" I think it makes sense to delete 'List' sheet column "G" and either
  1. use 'Management' sheet column 'C', or
  2. also delete 'Management' sheet column 'C' and add new column 'E' in the 'Renewal Email' sheet for "Group Email", and E1 would always be the address.
Do you have a preference or a different approach?

Also, are you saying that there never should be more than one email sent at the same time even if there is more than one Recipient in the Renewal Email sheet with a qualified Renewal Date?
I have no preference and yes all Bucs will be listed regardless of a qualifying renewal.  We decided that it make sense to keep all BUCs in the  loop Incase the absence or  in the event a BUC  resigns
Sorry but I'm not 100% sure what your answer means and because this is important, let me ask my question this way. With our currenet test data, Amanda gets an email that refers to quote Q-00000000 and Paul gets an email that refers to quotes Q-11111111 and Q-12345678.

In the new scheme will Salesforce.com (or you for now) get one email that refers to quotes Q-00000000, Q-11111111 and Q-12345678, or will two emails be generated, one that refers to quote Q-00000000 and another that refers to quotes Q-11111111 and Q-12345678?
I apologize for the confusion.  It will just be one email. And the table would have Amanda name associated to her quote(s) and same for Paul etc.
User generated imageThanks. This is a "first draft" of the new all-in-one email and there are some problems highlighted in yellow. Please tell me what you want to do about them. Also let me know if the data is the way you want it.

It also just occurred to me - shouldn't the Renewal Date appear in the email in addition to or instead of the End Date?
Here are my suggestions of changes, but I am flexible.  The main thing is to get the message across and that the table reflects the correct information.  Also, Team Name is spelled wrong and needs to be corrected.

Scanned-Documents.pdf
Will all the "about to expire on" always be the same for all the quotes in the email? If not then rather than putting it in the text, I think they should be shown as a new column in the quote details.

Another question. Some places we have SalesForce and others we have Salesforce. Which one is correct? I ask because I think it should be consistent.

BTW nice PDF!
Please see my previous post since I made a change to it.

  • In the attached workbook I changed the layout of  Renewal Emails.
  • While it’s only one email address, there’a Sub called ‘Test” that makes it easy to change what’s in F1 on the renewal Email sheet
  • Removed column ‘G’ from List sheet
  • The renewal email now contains a hidden attachment for the renewal data rather than converting the renewal data via HTML as was done before. Doing so eliminated the need to pad the Team Name and it corrected some alignment errors in the Total Price column
  • I believe I made the changes you requested but I modified the Subject. No matter if we use your suggestion or what I changed it to, I think the Subject and the current "Subscription(s) due to be renewed" should be consistent. In other words both should refer to some tense of either "renew" or "expire"
29167641.xlsm
Will all the "about to expire on" always be the same for all the quotes in the email? If not then rather than putting it in the text, I think they should be shown as a new column in the quote details.

I am thinking that if the auto notification is picking up only the quotes that will be expiring in 40 days would mean that the about to expire on date would be the same for all the quotes listed in the email.

Some places we have SalesForce and others we have Salesforce. Which one is correct? I ask because I think it should be consistent.

It should read "Salesforce"
I tested the workbook and it runs perfectly. The only correction is the spelling of the word reply.  

Also, I know that I may have asked this question previously, but just want to confirm again.  The email will auto send once it reaches the 40 day expiry time frame and as long as the workbook remains open or will I have to push the send email button to kick-off the notification?

Controls/Validations - I will open up a separate question for this and you may have something built in already,  but I would like to see if possible some controls/validation  built into the script in the event that our server has an issue or the workbook was not open causing the email notification not  send during the established date/time written in the script.  Currently Is the code written where it will send immediately once either of these possible issues have been resolved?
Also, I know that I may have asked this question previously, but just want to confirm again.  The email will auto send once it reaches the 40 day expiry time frame and as long as the workbook remains open or will I have to push the send email button to kick-off the notification?
Because of all the problems we had with the timer I think that only you can test it. To do that add a new quote that has a renewal date set to 41 days from today and then tomorrow at about 9 AM your time you should receive the email.

Currently Is the code written where it will send immediately once either of these possible issues have been resolved?
Yes. Note that I've made it so that you will never see the 'Start Timer' button. You should however still get the "Timer Started" message when you open the workbook.

I corrected the spellings of "reply" and "Team Name".
29167641a.xlsm
Because of all the problems we had with the timer I think that only you can test it. To do that add a new quote that has a renewal date set to 41 days from today and then tomorrow at about 9 AM your time you should receive the email.


Great!  I will perform the test.
You show cell S16669 having 5 pairs. How do I know how many pairs go in that column?

Muestras que la celda S16669 tiene 5 pares. ¿Cómo sé cuántos pares van en esa columna?

For row 18, should 1KEY, 2KEY, etcetera, be "627 273 731 31 1" or should it just be "627 273 731"?

Para la fila 18, ¿debería 1KEY, 2KEY, etcétera, ser "627 273 731 31 1" o debería ser simplemente "627 273 731"?
I encountered a run-time error on the start  timer upon downloading and enabling the latest workbook.

Error Message:
Run-time Error '1004'
Method ;OnTime' of 'object_Application' failed

Application.OnTimeValue("09:00:00"), "SendRenewalEmail"
This maybe for someone else's ask

You show cell S16669 having 5 pairs. How do I know how many pairs go in that column?

Muestras que la celda S16669 tiene 5 pares. ¿Cómo sé cuántos pares van en esa columna?
Yes, thank you.

Try this.
29167641b.xlsm
Did that correct the Run-time Error error?
Did that correct the Run-time Error error?

Unfortunately the error is still occurring.
In previous versions were you ever able to manually click the the Start Timer button without errors showing up?
In previous versions  were you ever able to manually click the the Start Timer button without errors showing up?

In the first workbook - 29167641.xlsm
I I no longer received the  Run-time error message after opening and enabling the workbook. But after clicking on the message box to start the timer and then clicking on the start timer button I was taken taken directly to the VBE and got the following code break error on "Stop"

Stop
Application.OnTime TimeValue("09:00:00"), "SendRenewalEmail"
With Sheets("Original Quotes").Shapes("btnTimer")
    .Visible = msoFalse
End With
MsgBox "Timer started"
Oh, sorry. Delete the line that says "Stop".
Removing the line "stop" did correct the issue.  I will proceed to setup and run the auto send test per your previous instructions
Results?
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No results yet.  I did not get a chance to test it out.  Will work on it today.
Good news!  The issue with the Start timer not  producing the message in the test.xlsm work per the instructions is now fixed.  I also tested the actual work book through changing the adjusting the time by a couple of minutes and it works also.

The problem was my MS Excel needed a repair.  Once it got repaired these issues went away.
I just tried to add a new quote and ran into a run-time error 9 - Subscript out of range.
If strParts(0)>0 Or strParts(1) >2 Or (strParts(1) ="2"And strParts(2)>0) Then

after resetting. I noticed that on the Renewal Email sheet.  The Renewal date showed up on row2 Col F and the End date
Thanks Martin!  The issue with the timer has been resolved.