VBA to select random email address

[benny1983]

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):

 Dim ol As Object
     Dim itm  As Object
     Set ol = CreateObject("Outlook.Application")
     Set itm = ol.CreateItem(0) 
     itm.To = "[e-mail]"
     itm.Subject = "BREACH NOTIFICATION"
     itm.Body = "Please review the attached identified breach."
     itm.Attachments.Add ("C:\Users\user1\Documents\Test1.pdf ")
 
     itm.Send  
     Set itm = Nothing 
     Set ol = Nothing

Any help would be appreciated!

[Rabbit]

Please use code tags when posting code or formatted data.

Do you want random or do you actually want alternating?

[benny1983]

Sorry - will be sure to use code tags in future.

[benny1983]

@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.

[Rabbit]

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.

[benny1983]

@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?

[Rabbit]

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{
:

activePerson = get id of person where the active indicator is true
send email to activePerson
set active indicator on activePerson to false
if activePerson is last person in table then
  update table and set first person in table as active
else
  update table and set next person in table as active
end if

[benny1983]

@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.

[Rabbit]

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.

[benny1983]

@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

Private Sub Command0_Click()
 
activePersonEmail = DLookup("Email", "QAEmailforBenForm", "Active = True")
 
     Dim ol As Object
     Dim itm  As Object
     Set ol = CreateObject("Outlook.Application")
     Set itm = ol.CreateItem(0)
     itm.To = activePersonEmail
     itm.Subject = "Test"
     itm.Body = "This is a test no action required."
 
     itm.Display
     Set itm = Nothing
     Set ol = Nothing
 
Dim sql As String
ssql = "UPDATE [QAEmailforBenForm] SET [Active]=False WHERE [Active]=True"
ssql = "UPDATE [QAEmailforBenForm] SET [Active]=True WHERE [Active]=False"
DoCmd.RunSQL ssql
 
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).

[benny1983]

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.

[twinnyfo]

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:

Dim ssql As String
ssql = "UPDATE [QAEmailforBenForm] SET [Active] = False WHERE [email] = '" & activePersonEmail & "';"
DoCmd.RunSQL ssql
ssql = "UPDATE [QAEmailforBenForm] SET [Active] = True WHERE [email] = '" & activePersonEmail & "';"
DoCmd.RunSQL ssql

Note, you also declared your variable incorrectly.

Hope this hepps!

[Rabbit]

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