Clicking an email address in a list form to send an email (1 Viewer)

mgmercurio

Member
Local time
Today, 17:01
Joined
Jan 18, 2021
Messages
55
I am sure this question has been asked many times, but I can not seem to find a thread that addresses it...so here goes.

I have a list box that is displaying a list of account names - col0, contact - col1 and col2 (first Name and Last name - 2 separate columns), and email addresses - col3.

I would like my user to be able to double-click one or many email addresses and load into an outlook email object to send the contacts an email.

Can anyone point me in the right direction?

Thanks!
-mgm
 

Isaac

Lifelong Learner
Local time
Today, 14:01
Joined
Mar 14, 2017
Messages
8,738
You might be able to automate outlook for that, use outlook automation code like CreateObject for the application, CreateItem for the mailitem, etc.
I'm not going to recommend hyperlinks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:01
Joined
Oct 29, 2018
Messages
21,358
Hi. If you want the user to select multiple contacts from a list, I am not sure you would be able to use a double click event. If so, you might consider using a button instead.

You should be able to use the SendObject method, if you don't want to use a hyperlink.
 

mgmercurio

Member
Local time
Today, 17:01
Joined
Jan 18, 2021
Messages
55
You might be able to automate outlook for that, use outlook automation code like CreateObject for the application, CreateItem for the mailitem, etc.
I'm not going to recommend hyperlinks
I agree...no hyperlinks. I will look into Outlook Automation. Thanks!
 

mgmercurio

Member
Local time
Today, 17:01
Joined
Jan 18, 2021
Messages
55
Hi. If you want the user to select multiple contacts from a list, I am not sure you would be able to use a double click event. If so, you might consider using a button instead.

You should be able to use the SendObject method, if you don't want to use a hyperlink.
I apologize...I should have been more articulate with my question. I I didn't mean to say double-click. However, I am not opposed to having the user select all email addresses - to highlight them, and then click a "Send Email" button to load all the selected adresses into an Outlook email message. In fact, that is probably the preferred way I would want to do it.

Is that what you are speaking of?

Thanks for the reply.
-mgm
 

mgmercurio

Member
Local time
Today, 17:01
Joined
Jan 18, 2021
Messages
55
Additional info to my question -

I was able to successfully setup a Combo-Box to effectively select one or many emails to populate another text box on the form using the Change_Click() method and then by using a button with a Docmd SendObject .... take the email address from the txt box and load it in to outlook.

This method works perfectly. I can even attach PDF's to the email and its fine...works really well. However, instead of the user have to hit the drop down arrow on the combo box and scroll through the list of addresses (over 3500), I would much prefer a list box for the user to scroll through and make their selection(s). It would be a lot easier for them. ;)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:01
Joined
Oct 29, 2018
Messages
21,358
Okay, make sure you use a Multi-Select Listbox and try the following code to gather all the email addresses into one String variable.
Code:
Dim strEmail As String
Dim var As Variant

With Me.ListboxName
    For Each var In .SelectedItems
        strEmail = strEmail & .ItemData(var) & ";"
    Next
End With

MsgBox strEmail
(untested)
Hope that helps...
 

mgmercurio

Member
Local time
Today, 17:01
Joined
Jan 18, 2021
Messages
55
Okay, make sure you use a Multi-Select Listbox and try the following code to gather all the email addresses into one String variable.
Code:
Dim strEmail As String
Dim var As Variant

With Me.ListboxName
    For Each var In .SelectedItems
        strEmail = strEmail & .ItemData(var) & ";"
    Next
End With

MsgBox strEmail
(untested)
Hope that helps...
Thank you. I am going to try this out. Just one question...
This code you provide is going in the change_Click() property along with a Sub function....then I will create another "send mail" button similar to the one I did for the combo box and program the button take the output from the MultiSelect ListBox and place into a outlook email message... correct?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:01
Joined
Oct 29, 2018
Messages
21,358
Thank you. I am going to try this out. Just one question...
This code you provide is going in the change_Click() property along with a Sub function....then I will create another "send mail" button similar to the one I did for the combo box and program the button take the output from the MultiSelect ListBox and place into a outlook email message... correct?
Up to you. You can create a new button to test the code and use its Click event. Once you get it working, you can copy or move the code to wherever you need it to be for your users to use.
 

isladogs

MVP / VIP
Local time
Today, 21:01
Joined
Jan 14, 2017
Messages
18,186
Just for info, you can also do this without using Outlook.
An alternative approach called collaborative data objects (CDO) can be used to send emails direct from Access.
The multiselect listbox code would be identical in each case
 

mgmercurio

Member
Local time
Today, 17:01
Joined
Jan 18, 2021
Messages
55
Up to you. You can create a new button to test the code and use its Click event. Once you get it working, you can copy or move the code to wherever you need it to be for your users to use.
Ok DBGuy...Im going to apologize upfront...I have no clue what I am doing here....BUT...I did manage to "trial and error" my way through some things and I found a couple interesting points.


I placed the below code into a button named, bt_SendEmail. At first, when the button was clicked, it threw an compile error up complaining abut " For Each var In .SelectedItems" line. After researching a bit, I found the List box collection is called .ItemsSelected I just found this by pure happenstance and unless you were meaning something totally different than what I am assuming...I guess it was just transposed.

But regardless....after I made the change to your code, I then fired off the button and it worked....sorta. Meaning ...your code did exactly what it was supposed to do...It took the various selected emails that I chose in the List box and produced a Access Message box listing all the addresses chosen. My options on the message box was OK and Close. :) So....your code worked...however, after I clicked ok...the code went on to follow through the rest of the command and brought my outlook message window with all the specifics I have in my code. But...sad to say.....no email addresses :(

So then I commented out your msgbox statement and tried it again. This time it went straight to bringing up my outlook message form with all my specifics....but again...no email addresses.

So again, I apologize...I really don't know what I'm doing. Can you take a look at the code below and tell what I am doing wrong?

oh and one more note.... my MultiList Box is named EmailListBx. One more variable I want to throw in the mix is...right now, my List box only contains 1 column - the email address However, i plan to list the Account Name and First and Last Name of the contact so this means ultimately I will have 4 columns in the list box with the email address being in column 3 AccountName = 0 FirstName =1 LastName = 2 EmailAddress = 3

Thanks again for everything...I certainly appreciate your time and effort.

Private Sub bt_SendEmail_Click()

Dim strEmail As String
Dim var As Variant

With Me.EmailListBx
For Each var In .ItemsSelected
strEmail = strEmail & .ItemData(var) & ";"
Next
End With

'MsgBox strEmail

DoCmd.SendObject acSendNoObject, , , Me.EmailListBx.Value, , , "This is a test message subject", "This is the body of my email message. Hello World.", True



End Sub
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 14:01
Joined
Oct 29, 2018
Messages
21,358
Hi. I didn't get a chance to test the code I posted earlier (I did mark it "untested") because I was using my phone to post it (as I am also doing right now with this post).

To have the selected email addresses show up in your Outlook email, you'll need to use the String variable in your email code.

For example, in your SendObject code, replace Me.EmailListBx.Value with strEmail.
 

mgmercurio

Member
Local time
Today, 17:01
Joined
Jan 18, 2021
Messages
55
Hi. I didn't get a chance to test the code I posted earlier (I did mark it "untested") because I was using my phone to post it (as I am also doing right now with this post).

To have the selected email addresses show up in your Outlook email, you'll need to use the String variable in your email code.

For example, in your SendObject code, replace Me.EmailListBx.Value with strEmail.
Ohhhhh I'm such a goober sometimes..... yep I see that...and yes it now works! Thank you very much for this. I really appreciate your help with this.
 

mgmercurio

Member
Local time
Today, 17:01
Joined
Jan 18, 2021
Messages
55
Just for info, you can also do this without using Outlook.
An alternative approach called collaborative data objects (CDO) can be used to send emails direct from Access.
The multiselect listbox code would be identical in each case
Thank you isladogs! I am going to dig into CDO and see what i can do ;)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:01
Joined
Oct 29, 2018
Messages
21,358
Ohhhhh I'm such a goober sometimes..... yep I see that...and yes it now works! Thank you very much for this. I really appreciate your help with this.
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

isladogs

MVP / VIP
Local time
Today, 21:01
Joined
Jan 14, 2017
Messages
18,186
You're welcome...and thanks for the email you just sent -I've just replied
 

Users who are viewing this thread

Top Bottom