hyperlink field to send email

[Linda in Iowa]

I have a membership database with a field that stores email addresses. Is
there a way to add the mailto: to each record? If I create a hyperlink
field, then I have to open the hyperlink and edit it by adding the email
address to each record. If I use the send object as a macro then I need to
add the email address for each record. If I use the sendobject as a macro
is there a way to automatically enter the email address for each record?
Also if I can use VB and code as DoCmd.sendobject can I have the email
address entered into the address of the email? thanks again.

 

[Rose B]

Hi Linda,

in the 'after update' event for that field you can change the content of the
field by adding something like Me.fieldname = Replace(Me.fieldname,
"http://", "mailto:").


Try setting the recipient for your e-mail = HyperlinkPart(Me.fieldname,
acDisplayedValue). Not sure exactly how you are creating/sending the e-mail
but hopefully this might help.

Good luck!

Rose

 

[Linda in Iowa]

the original database has the email address field as text. We want to be
able to email the person when they have not paid dues. I changed the field
to hyperlink in the design mode and then put the code you have for the after
update event into the on click event. With the database open to a record I
then clicked on the email field and it changed from http:// to mailto: and
opened the email program with that person' email address entered in the To:
area. That works about as good as I think it can get. We don't want to
use any attachments so will still have to add the subject and the body info.

Is there a code I can enter somewhere that will automatically enter the
subject info?

Thanks.

 

[Rose B]

Yes, there is! I can't recall now where I got this from (could have been this
forum or a text book!) but below is some code that I use to set up the
recipient, subject and then the text of e-mails and before sending them. (It
is not the complete code as this would be a bit long but I hope that this
helps.)

I am not sure whether the on click event is what you really want to use -
that's OK if you want to do each person individually, but if not, why don't
you create a recordset that contains all of the people who have not paid
their dues, then you can loop through the records and send an e-mail
automatically to each one. Just in case it is of any use, the code below does
something similar - but as I said, it isn't all there as I have omitted some
of the code that is very specific to my application (in this case it is to
remond people to send in their expenses). Hope I haven't cut out any
important bits!!!!!

Private Sub SubmitMonthlyExpenseReminder_Click()

Dim mySQL As String
Dim appOutlook As Outlook.Application
Dim appOutlookMsg As Outlook.MailItem
Dim appOutlookRecip As Outlook.Recipient
Dim strMsgBody As String
Dim strRecipient As String
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRS As New ADODB.Recordset

mySQL = "SELECT statement to get your data"

myRS.Open mySQL, cnn1, adOpenForwardOnly, adLockReadOnly

If myRS.EOF = True And myRS.BOF = True Then
MsgBox "There are no jobs waiting for expense details"
GoTo Error:
End If


Do While myRS.EOF = False
' put code in here to set any other variables etc. you may need. You can
pick up fields from the SELECT statement by using myRS.Fields(0) thru
myRS.Fields(n) based upon sequence is statement

Set appOutlook = CreateObject("Outlook.Application")
Set appOutlookMsg = appOutlook.CreateItem(olMailItem)

With appOutlookMsg
Set appOutlookRecip = .Recipients.Add(strRecipient)
appOutlookRecip.Type = olTo
.BCC = "email if you want to bcc anyone - perhaps yourself"
.Subject = "Subject text is here"
.Importance = olImportanceHigh
' Above is set to imortance high - you may want this if you are chasing money!
strMsgBody = "Dear " & myRS.Fields(8) & "," & vbCrLf & "Please
find listed below details of jobs you have undertaken and for which we
require your expenses." & vbCrLf
' Above code you would change for your particular circumstances



appOutlookMsg.Body = strMsgBody & vbCrLf & vbCrLf & "Please
provide the following information for each job within the next 4 working
days;" & vbCrLf & vbCrLf & "Many thanks"
.Send

Set appOutlook = Nothing
Set appOutlookMsg = Nothing
Set appOutlookRecip = Nothing
myRS.MoveNext

Loop

Finish:
myRS.Close
cnn1.Close


Error:
End Sub

 

[Linda in Iowa]

You are most helpful and I will try to figure out how to use this
information. I am not very proficient at this, just a hobby because I
learned some of it in school over 10 years ago. I am retired now and help a
couple people with volunteer positions using Access databases.
I do have a query that will pull up the expired members and show them in a
form so that is probably where I want to use this code. What if the user is
not using Outlook for email? I would like it to work wiwth any email
program as I use Outlook Express.

 

[Rose B]

Well good luck Linda! I am in much the same position as you - learning all
the time, also retired and writing some Access stuff as a volunteer, but my
programming experience originally was over 25 years ago and in Cobol!!! So -
take heart, this forum is great and there is also some good advice on the
Microsoft web site, and some good books out there too. I usually find my way
through most things eventually - might not be in the most efficient or
elegant way initially!

I am afraid that I do not know about other e-mail programs as I use Outlook,
as do the folk at the places I have written applications for. Might be worth
searching for the specific applications used, and you may need to be able to
test to see which mail program the user has available - not sure. The other
thing to mention is that you need to ensure that you have the necessary
libraries available to you (in VB look under Tools/References to see what you
have available, and for Outlook scroll down to find the Microsoft Outlook
ones).

 

[Peter Hibbs]

Linda,

You could use the code at :-

http://www.ostrosoft.com/OSSMTP6.asp

to send an email without using MS Outlook or Outlook Express (or
whatever). This option does requires a DLL file to be installed and
registered on your PC however. If you need any code to send the email
I can post it here for you, just let me know if you need it.

HTH

Peter Hibbs.