473,320 Members | 1,799 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

VBA to select random email address

Hi All, I have some VBA code attached to a button which populates a report to be reviewed by one of my team.

I somehow need to make sure these requests are alternated between the two team members and therefore cannot hardcode the email address into the code.

Is there any way I can alternate who the email is sent to? Currently I have something like this (note this is part of my code and ot the entire thing):

Expand|Select|Wrap|Line Numbers
  1.  Dim ol As Object
  2.      Dim itm  As Object
  3.      Set ol = CreateObject("Outlook.Application")
  4.      Set itm = ol.CreateItem(0) 
  5.      itm.To = "email1@address.com"
  6.      itm.Subject = "BREACH NOTIFICATION"
  7.      itm.Body = "Please review the attached identified breach."
  8.      itm.Attachments.Add ("C:\Users\user1\Documents\Test1.pdf ")
  9.  
  10.      itm.Send  
  11.      Set itm = Nothing 
  12.      Set ol = Nothing
Any help would be appreciated!
Nov 3 '14 #1
12 1387
Rabbit
12,516 Expert Mod 8TB
Please use code tags when posting code or formatted data.

Do you want random or do you actually want alternating?
Nov 4 '14 #2
Sorry - will be sure to use code tags in future.
Nov 4 '14 #3
@Rabbit
Sorry - no problems (still only new and learning).

I'm actually after alternating emails. So each time I send it for review it moves to the next team member and emails them.

Thanks for your help.
Nov 4 '14 #4
Rabbit
12,516 Expert Mod 8TB
Assuming you have a table of your team members, as a column that indicates who is the active member. After it sends the email to the active member, have the code update to the next person.
Nov 4 '14 #5
@Rabbit
Sounds good, but I wouldn't have a clue how to do any of that. I have a concept of VBA but not much experience.

What should the code look like?
Nov 4 '14 #6
Rabbit
12,516 Expert Mod 8TB
Then before you take on a task like this, you will want to learn some VBA. You can start with this tutorial: http://www.functionx.com/vbaccess/Lesson01.htm

In psuedo-code, what you'll want to do is something like this{
:
Expand|Select|Wrap|Line Numbers
  1. activePerson = get id of person where the active indicator is true
  2. send email to activePerson
  3. set active indicator on activePerson to false
  4. if activePerson is last person in table then
  5.   update table and set first person in table as active
  6. else
  7.   update table and set next person in table as active
  8. end if
Nov 4 '14 #7
@Rabbit
Thanks mate =- coming back to this thread after developing some more of my database. I have a good idea of the VBA but would appreciate perhaps a working example?
Any chance you can write what the code would actually look like in VBA just making up the names so I can get a good idea on how to adapt this to my project?
Cheers.
Nov 25 '14 #8
Rabbit
12,516 Expert Mod 8TB
We try not to write the code for the posters because it is a missed learning opportunity. Rather, we prefer that you make an initial attempt at the code and we can walk you through any mistakes you may have made. We find that this helps the poster learn better so they are in a better position to solve problems in the future.
Nov 25 '14 #9
@Rabbit
Ok, I take your point. Here is what I have done but can't get it to function properly. I didn't quite know how to set a record as active (given this function wont be all performed at once so the focus on an active person will move, the form will be closed but next time you open the form I want it to be the same active person it was prior to being closed).

Trying to think outside the box a little to try and get it to work as there is only 2 people on my list but needing it to alternate between them every time the form is opened.

I know this is not really what you meant but I wasn't sure where to start. I need to make this function improrovement asap as it's currently skewing my results having only one person review each result.

I'm happy to take on board any feedback -please guide me though as I'm not a natural coder but learn very quickly once guided through

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3. activePersonEmail = DLookup("Email", "QAEmailforBenForm", "Active = True")
  4.  
  5.      Dim ol As Object
  6.      Dim itm  As Object
  7.      Set ol = CreateObject("Outlook.Application")
  8.      Set itm = ol.CreateItem(0)
  9.      itm.To = activePersonEmail
  10.      itm.Subject = "Test"
  11.      itm.Body = "This is a test no action required."
  12.  
  13.      itm.Display
  14.      Set itm = Nothing
  15.      Set ol = Nothing
  16.  
  17. Dim sql As String
  18. ssql = "UPDATE [QAEmailforBenForm] SET [Active]=False WHERE [Active]=True"
  19. ssql = "UPDATE [QAEmailforBenForm] SET [Active]=True WHERE [Active]=False"
  20. DoCmd.RunSQL ssql
  21.  
  22. End Sub
I can get it to email to the current selected person but not able to change the check box indicating that person is active (it will check one but not uncheck the other one concurrently).
Nov 26 '14 #10
Have a good laugh at my code whilst reviewing it. I do have a current VBA guide I am reading through but I need a fix for this asap and unsure which sections to quickly refer to to get an answer.
Nov 26 '14 #11
twinnyfo
3,653 Expert Mod 2GB
benny,

Lines 18-19 kind of negate each other. You need to identify which email it is that you are switching back to False/True. You should be running two SQL strings:

Expand|Select|Wrap|Line Numbers
  1. Dim ssql As String
  2. ssql = "UPDATE [QAEmailforBenForm] SET [Active] = False WHERE [email] = '" & activePersonEmail & "';"
  3. DoCmd.RunSQL ssql
  4. ssql = "UPDATE [QAEmailforBenForm] SET [Active] = True WHERE [email] = '" & activePersonEmail & "';"
  5. DoCmd.RunSQL ssql
Note, you also declared your variable incorrectly.

Hope this hepps!
Nov 26 '14 #12
Rabbit
12,516 Expert Mod 8TB
You're missing a piece, and that's a field that indicates the order of assignment. So everyone needs to be assigned a sequential number from 1 to n where n is the number of emails there are. You then also need to retrieve that current number that is set to true and also the max number. After you set the email, you set the current to false. If the current number is equal to the max number, set number 1 to true. Otherwise, add 1 and set that to true.

If you look at my psuedo code, you are missing lines 1 and 4-8
Nov 26 '14 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: news | last post by:
There's all kinds of ways to validate an email address to make sure it's well formed and whatnot, but what about checking to see if it's a valid e-mail account? Like how you can use checkdnsrr()...
3
by: scott_baird | last post by:
I have an email macro setup (maybe I should go another way, but that was the quickest at the moment...) and what I would like to do is automate the "to" addressee of the email it generates for...
11
by: Brian Henry | last post by:
I have a domain cluster with AD running, and I want to lookup a users email address (exchange 2000 server is integrated with the AD system) so i can email the user based on their user name. does...
1
by: Owen Richardson | last post by:
I just started using The Login Objects and the CreateUserWizard creates a number of fields including Email address and i cant figure out how to reference it: intellisense tells me that...
2
by: e_matthes | last post by:
Hello everyone, I am about to implement an authentication system on the site I am building. I don't care much what users choose for their username and password, but I want to verify that users...
1
by: terry_wall | last post by:
I am trying to add a cmdButton to an Access form. I would like to click the button, look up an email address in a database table, and execute Outlook with that email address ready to send an...
4
by: kang jia | last post by:
hi i am doing mailinglist currently. the code in my first page is like this : : <html> <head> <link rel="stylesheet" type="text/css" href="gallery.css" /> <script language="JavaScript"> ...
2
by: jujutsumaster | last post by:
I have a table containing "AgentName" and "AgentEmailAddresss". I need to loop through the table and get each "AgentEmailAddress" in turn and use it as a query filter for "MyQuery" then use it to...
6
by: =?Utf-8?B?RGF2aWQsIHRoZSBncmVhdA==?= | last post by:
Hi, Is there a way to validate an email address and to check whether it is sendable? Function EmailTo(ToEmail, FromEmail, strSubject, strBody) Set objMessage = CreateObject("CDO.Message")...
8
by: penfold33 | last post by:
Hi I need to generate a CSV of names and address from a table and I would like only one result (it doesn't matter which) per distinct email address. If possible, they also need to be ordered by...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.