Link to send an email! 4

[LooneyToon]

Lads,
I have a list of clients in a table/form. Each one of these clients has an email address as one of the fields in the table. What I would like to do is to be able to click the email address of a client in the form and for Outlook to open with a new email message addressed to that person!

Any ideas ?

Thanks

 

[Newposter]

The field content should be "mailto:soandso@domain.com" and it will automatically open a blank Outlook message addressed to that person. Newposter
"Good judgment comes from experience. Experience comes from bad judgment."

 

[LooneyToon]

you see the prob is that I want the user to just type in the email address when entering in the Client details, then i have a separate form and i want the user to be able to click on the email address to send an email!

Looney Toon

 

[danvlas]

Sub TextBoxName_Click()
DoCmd.SendObject , , , [TextBoxName], , , "Subject line", "MessageText", True
End Sub

That will do.

Regards,

Dan

 

[captvideo33065]

Did you ever get your email issue to work? I'm having the exact same problem. I read through the threads on your question but I couldn't quite figure it out. If you got it to work and could explain it to me I would be most thankful.
captvideo33065

 

[nat1967]

Hi captn,...

I had trouble with the above code also, so... i fixed it. this code below does work when you click the textbox. i just tried it.

'***begin code*****

Private Sub TxtEmail_Click()
Dim strRecipient As String

strRecipient = Me!TxtEmail

DoCmd.SendObject , , , strRecipient, , , "Message Subject", , True


End Sub
'***end code****

This code will launch outlook and take the email address from the textbox (txtemail), place it in the to: field, and let the user add what ever text they need to.

HTH

Nathan

 

[BLBurden]

nathan1967 you are a gift from God. If you knew how long I have been trying to get this done, and how many hours I have gone through this forums, you just would not believe it. And it's funny how I found this tip you give back in Oct, 2002

nathan1967 (TechnicalUser)
Oct 1, 2002

Private Sub TxtEmail_Click()
Dim strRecipient As String
strRecipient = Me!TxtEmail

DoCmd.SendObject , , , strRecipient, , , "Message Subject", , True
End Sub

I was searching Ask Jeeves, and I saw an article that said Link to send an email. and there was my answer that I had been serarching foever for. Thanks agan.

All I need to do is now learn how to linke my form or report to that email and I will be cooking with gas.

Sign forever grateful
Brenda

 

[nat1967]

hi brenda,

thanks for you kind words!

To send a query or report, just modify the sendobject to something like this:

'*****example****
'acSendQuery tells Access you are using a Query
'"Test" is the name of the query
'acFormatRTF is the format for the attachment
'same info applies to reports
'acSendReport and be sure to show the name of the report
DoCmd.SendObject acSendQuery, "test", acFormatRTF, _
strRecipient, , , "NorthWind Traders ", _
"Here is our list of tasty products",,True Have A Great Day!!!,

Nathan
Senior Test Lead

 

[Rmcta]

Hello to you all!
I have a query (qryMustContact) that will find all the email addresses of those people I need to send a message.
How can I modify the code so that it reads the addresses from my query and sends the same message to all of them?
So far I have just created a form with a command button (cmdSendMessage)

Thank you

 

[MoJoP]

Hi Rmcta

I have a database with a command botton that sends a message to various people in my database query. Because there are so recipients, it is "cleaner" to include them in the BCC line of the e-mail and send it to myself in the To line. I've shown my example below but you need to know some stuff to understand what I've done: in my database I didn't want to hard code too many items (the database is for a client) so I have a utilities area where they change various data like Executives of the Committee (just showing Member ID number and then can link to the person' membership data) and a form to enter how they wish the string message in the e-mail to be worded. So when you see references to other forms, that is why. Also, my internet provider limits the number of recipients to 100 so that is why I have the fields for intstart and intend - I am prompted to enter the Member ID numbers so I don't have more than 100 recipients at a time.

Dim db As Database
Dim rst As Recordset
Dim strsql As String
Dim strNames As String
Dim strChairE As String
Dim strComme As String
Dim strMess1 As String
Dim strMess2 As String
Dim strMess3 As String
Dim strMess4 As String
Dim strMess5 As String
Dim strMess6 As String
Dim strMess7 As String
Dim strMessage As String
Dim strAgendaLink As String
Dim strNewsletterLink As String
Dim strRegisLink As String
Dim strEdDay As String
Dim strPassword As String
Dim strMonth As String
Dim strComm As String
Dim strTreas As String
Dim strTreasF As String
Dim intstart As Integer
Dim intend As Integer

intstart = InputBox("Enter start Mem ID of mailouts&quot
intend = InputBox("Enter end Mem ID of mailouts&quot
strTreas = DLookup("[First Name]", "Members", "[Auto] = " & DLookup("[ExecID]", "tblExecutive", "[Position] = 'Treasurer/Education Day Coordinator'&quot) & " " & _
DLookup("[Last Name]", "Members", "[Auto] = " & DLookup("[ExecID]", "tblExecutive", "[Position] = 'Treasurer/Education Day Coordinator'&quot)
strTreasF = Format(DLookup("[Fax Number]", "Members", "[Auto] = " & DLookup("[ExecID]", "tblExecutive", "[Position] = 'Treasurer/Education Day Coordinator'&quot), "(@@@) @@@-@@@@&quot
strComm = DLookup("[First Name]", "Members", "[Auto] = " & DLookup("[ExecID]", "tblExecutive", "[Position] = 'Communications Coordinator'&quot) & " " & _
DLookup("[Last Name]", "Members", "[Auto] = " & DLookup("[ExecID]", "tblExecutive", "[Position] = 'Communications Coordinator'&quot)
strComme = DLookup("[Email Address]", "Members", "[Auto] = " & DLookup("[ExecID]", "tblExecutive", "[Position] = 'Communications Coordinator'&quot)
strPassword = DLookup("[Password]", "tblUtilities&quot
strAgendaLink = DLookup("[AgendaLink]", "tblUtilities&quot
strNewsletterLink = DLookup("[NewsletterLink]", "tblUtilities&quot
strRegisLink = DLookup("[RegistrationLink]", "tblUtilities&quot
Set db = CurrentDb
strsql = &quot;SELECT Members.Auto, Members.[Email Address], Membership.Current, Members.NoListServe, Members.Discontinue FROM Members LEFT JOIN Membership ON Members.Auto = Membership.MemID WHERE ((Not (Members.[Email Address]) Is Null) AND ((Membership.Current)=Str(DMax('[Current]','Membership'))) AND (Members.Discontinue)=False) And Members.Auto >= &quot; & intstart & &quot;And Members.Auto <= &quot; & intend
Set rst = db.OpenRecordset(strsql)
strChairE = DLookup(&quot;[Email Address]&quot;, &quot;Members&quot;, &quot;[Auto] = &quot; & DLookup(&quot;[ExecID]&quot;, &quot;tblExecutive&quot;, &quot;[Position] = 'Chair'&quot)
strMess1 = &quot;Hello UMNO Member&quot; & Chr(10) & Chr(13) & Chr(10) & Chr(13)
strMess2 = DLookup(&quot;[MemMemo1]&quot;, &quot;tblMemMemos&quot & Chr(13) & Chr(10) & Chr(13) & Chr(10)
strMess3 = &quot;Agenda: &quot; & strAgendaLink & Chr(13) & Chr(10) & &quot;Registration Form: &quot; & strRegisLink & Chr(10) & Chr(13) & &quot;Newsletter: &quot; & strNewsletterLink & Chr(13) & Chr(10) & Chr(13) & Chr(10)
strMess4 = DLookup(&quot;[MemMemo2]&quot;, &quot;tblMemMemos&quot & &quot; &quot; & strTreas & &quot; at &quot; & strTreasF & &quot;.&quot; & Chr(13) & Chr(10) & Chr(13) & Chr(10)
strMess5 = DLookup(&quot;[MemMemo3]&quot;, &quot;tblMemMemos&quot & &quot; &quot; & strComm & &quot; at &quot; & strComme & &quot;.&quot; & Chr(13) & Chr(10) & Chr(13) & Chr(10)
strMess6 = DLookup(&quot;[MemMemo4]&quot;, &quot;tblMemMemos&quot & Chr(13) & Chr(10) & Chr(13) & Chr(10)
strMess7 = &quot;UMNO Executive&quot;
strMessage = strMess1 & strMess2 & strMess3 & strMess4 & strMess5 & strMess6 & strMess7

With rst
.MoveFirst
strNames = ![Email Address] & &quot;;&quot;
.MoveNext
Do Until .EOF
strNames = strNames & ![Email Address] & &quot;;&quot;
.MoveNext
Loop
End With
DoCmd.SendObject acSendNoObject, , , strComme, , strNames, &quot;UMNO Mail Out to Members&quot;, strMessage, -1

Sorry for the wordy example - hope this helps!

MoJoP

 

[Rmcta]

Wow MoJoP you really impressed me with your long and colorful message.
Is there a simpler version for me (a user with not much experience) and for my simpler needs?
1 message to approx. 25 users?

Thank you

 

[BLBurden]

Hey Nathan1967, I tried to do your example and it returned a runtime error 2302 (Microsoft Access can't save the output data file you've selected). But I already know how to send a report or query to Outlook, but I guess my continuous problem is, which probable impossible, because I haven't gotten an answer too is, &quot;IS IT POSSIBLE TO JUST SEND ONE RECORD?&quot; from a report that has 2 or more records in it.

Example of my problem, Mary Smith calls in a trouble call, I input the data she gives me, into my form, I send Mary Smith and email with the attached (form/report), showing/ telling her that her ticket has been processed, and later another email just telling her it was completed.

This form is used for ever call that comes in, and all the calls are different problem, so I can't you a generic form. I dont wont Mary Smith to see all the other records, just the one she called in about.

So that's my problem in a nutshell.

Thanks in advance
Always grateful

 

[nat1967]

Hi BLBurden,

There is not a lot to go on here as far as detail. There has to be something distinct about the record in order to select just one.

For instance, if your record has fields like &quot;name&quot;, &quot;trouble open&quot;, trouble close&quot;, you could have your email send the correct report. your queries would be done by emailing report &quot;A&quot; when the ticket is opened. Then follow up with a query to send report &quot;B&quot; when closed.

I guess all this is to say, yes, its possible but there are probably several different ways to do it depending on how you are keeping information. Have A Great Day!!!,

Nathan
Senior Test Lead

 

[nat1967]

Hi Rmcta,

In order to send your email to everyone, you will need to create a loop and execute it until you have reach the end of the recordset created by your query.

Here is an example of sending to a list using Outlook:

'****begin code****
Private Sub Command0_Click()
On Error Resume Next

Dim rs As DAO.Recordset
Dim strEMail As String
Dim strFName As String
Dim strLName As String
Dim strMessage As String
Dim db As DAO.Database
Dim outApp As Outlook.Application
Dim outmessage As Outlook.MailItem

Set outApp = CreateObject(&quot;Outlook.application&quot

Set db = CurrentDb()

'builds the recordset based on your query name. Here the query is LateNoticeQuery.
Set rs = db.OpenRecordset(&quot;LateNoticeQuery&quot;, dbOpenDynaset)

'begins loop
Do Until rs.EOF = True

Set outmessage = outApp.CreateItem(olMailItem)

strFName = rs.Fields.Item(&quot;FirstName&quot
strLName = rs.Fields.Item(&quot;LastName&quot

'builds the body of the email
strMessage = &quot;Dear&quot;
strMessage = strMessage & &quot; &quot; & strFName & &quot; &quot; & strLName
strMessage = strMessage & &quot; &quot; & &quot;Our records indicate you are late!!&quot;

'retrieves email address
strEMail = rs.Fields.Item(&quot;E_Mail&quot

'moves to the next record
rs.MoveNext

outmessage.To = strEMail
outmessage.Subject = &quot;Out of Balance&quot;
outmessage.Body = strMessage
outmessage.Send
Set outmessage = Nothing

'recycles loop
Loop

Set outApp = Nothing
Set rs = Nothing

'annouces all are sent
MsgBox &quot;All e-mails have been sent&quot;, vbInformation, &quot;Notice&quot;

End Sub
'*****end code***** Have A Great Day!!!,

Nathan
Senior Test Lead

 

[nat1967]

Hi Brenda,

I had one too many commas. here is a correct docmd:

DoCmd.SendObject acSendReport, &quot;latenoticereport&quot;, acFormatRTF, _
&quot;recipemailaddress&quot;, , , &quot;NorthWind Traders &quot;, &quot;Here is our list of tasty products&quot;, True Have A Great Day!!!,

Nathan
Senior Test Lead

 

[BLBurden]

Nathan1967, forgive me I am not dealing with a full deck, Access have just fried my brain. To answer your question about the record fields:

Some of the fields are 'TicketNo, CustomerID, Dates opened/closed, etc. I was wondering if one of these can be associated with the email ComboBox to send that report I talked about in previous post with you.

Always grateful
Brenda

 

[BLBurden]

I have only been in this forum since Jan 13th, and I was about to throw in the towel, because I am not a programmer,or a very technical person, I am just a person that is willing to learn, to whomever have the patient to teach/show me.

Nathan1967 rescured me, and gave me hope, that there is still someone out there who is willing to help and make it really simple and easy to understand.

Kudos to Nathan1967 and all the others that have helped me.

Always grateful
Brenda

 

[Rmcta]

Thank you very much Nathan. I will try that.

 

[nat1967]

Hi Brenda,

It might be easier if I send you an example database to demonstrate a way of doing what you need. Let me know if you are interested and where to email it. The explanation could get real wordy here but a visual reference will probably be easier.

Just let me know. Have A Great Day!!!,

Nathan
Senior Test Lead

 

[nat1967]

Rmcta,

I look forward to hearing about your success. Have A Great Day!!!,

Nathan
Senior Test Lead