By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

Hyperlink Follow to send e-mail from Access 2007

P: 99
I'm really sorry if I posted this already. I'm new to the site, and as my Q doesn't seem to have appeared I think maybe I just previewed it and didn't actually post it.

I want to send e-mails from inside an Access application. I've been experimenting and so far the following does sort-of work!
Expand|Select|Wrap|Line Numbers
  1. 'address is a field containing an e-mail address
  2.  
  3. Private Sub Send_Click()
  4.     Dim ctl As CommandButton
  5.     Set ctl = Me.Send
  6.     With ctl
  7.         .HyperlinkAddress = "mailto:" & address & "#"
  8.     End With
  9. End Sub
When I click the button an Outlook new message appears, with the address neatly filled in. (I can get multiple addresses in, too, and a subject line, so that's all fine). BUT Outlook 2007 (horrible thing!) is not my default e-mail program ... That's BT Yahoo! Mail (nice thing!)

Is there a reasonably simple way of getting Access to go for the default e-mail program instead of Outlook (or is this just Microsoft being awkward?!) Apart from my preferences, I want the DB to be portable to any other machine running a reasonably recent version of the Microsoft opsy.
Jan 8 '10 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
There is a complicated module somewhere I think. I'll see if I can find it for you.

BTW, the end tag on code tags is /CODE not END CODE :D
Jan 8 '10 #2

ADezii
Expert 5K+
P: 8,638
Try
Expand|Select|Wrap|Line Numbers
  1. Private Sub Send_Click()
  2. With Me![Send]
  3.   .HyperlinkAddress = "mailto:" & Address
  4.   .Hyperlink.Follow
  5. End With
  6. End Sub
Or
Expand|Select|Wrap|Line Numbers
  1. Private Sub Send_Click()
  2. Application.FollowHyperlink "mailto:" & Address
  3. End Sub
Jan 8 '10 #3

P: 99
Thanks. However ... !!
Your version 1 was what I tried first (before raising my original query with Bytes). It very strangely opens TWO Outlook new messages??? And, sadly, your version 2 does exactly the same thing. In both cases the behaviour seems to me to be totally unintuitive. I don't like programming at this high level, it's so hard to figure out what the system is "thinking", isn't it?
Jan 9 '10 #4

P: 99
Thanks, that would be good ... maybe! It depends how 'complicated' it is. I have a fairly fierce rule that I don't use code I don't (even vaguely) understand. When I have done and later something goes wrong I've no idea what to do about it!! Nevertheless, I'd love to have it if you can find it.
Jan 9 '10 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Here are some previous post threads you can look at.

having access send an email, email automation

Automating Emails using CDO

Send mail by CDO and attachments

You'll find most of these solutions are based on Paul Sadowski's code.

http://www.paulsadowski.com/WSH/cdo.htm#LoadFromFile
Jan 9 '10 #6

P: 99
Thanks ... I'm still trying to fathom out whether I understand what's going on in Paul Sadowski's code! I'll certainly give it a go!
Jan 10 '10 #7

NeoPa
Expert Mod 15k+
P: 31,492
Don't blame the system too much for being kludgy. This all used to work fine until they had to add in the security measures to protect against malicious code writers. Good luck anyway and Welcome to Bytes! :)

BTW If you did post the question before it's not there now. I suspect your original assumtion was 100% and it was just previewed.
Jan 11 '10 #8

P: 99
I'm still wrestling with Outlook! The following code makes a string of e-mail addresses called 'mailstr', pulling addresses out of EMAILING_TABLE, andseparating them by ';'. It then opens a new Outlook message (does that ok), and is supposed to put the string 'mailstr' of all the addresses in the To: field of the message.

However, in the To: field, the string of addresses is cut short in the middle of an address. The message box in the code correctly reports the string, so the problem is definitely in the Outlook bit. Is there a maximum number of recipients you can put in the To: field of a message???

Expand|Select|Wrap|Line Numbers
  1. Private Sub SendGroupEmail_Click()
  2.     Dim ctl As CommandButton
  3.     Set ctl = Me.SendGroupEmail
  4.  
  5.     'declare a string to hold the addresses
  6.     Dim mailStr As String
  7.     mailStr = ""
  8.  
  9.     'declare and open a recordset for the table containing the addresses
  10.     Dim rstEmail As Recordset
  11.     Dim db As Database
  12.     Set db = CurrentDb
  13.     Set rstEmail = db.OpenRecordset("EMAILING_TABLE", dbOpenDynaset)
  14.     If Not (rstEmail.EOF And rstEmail.BOF) Then
  15.         rstEmail.MoveFirst
  16.         'loop thro the recordset, appending each e-mail to the mailStr
  17.         While Not (rstEmail.EOF)
  18.             If Not (rstEmail!EmailAddress = "") Then
  19.                 mailStr = mailStr & rstEmail!EmailAddress & ";"
  20.             End If
  21.             If Not rstEmail.EOF Then
  22.                 rstEmail.MoveNext
  23.             End If
  24.         Wend
  25.         If Not (mailStr = "") Then
  26.             'knock the final semi-colon off the end of the mailStr
  27.             mailStr = Left(mailStr, Len(mailStr) - 1)
  28.         End If
  29.     End If
  30.     'debug messages
  31.     If Not (mailStr = "") Then
  32.         MsgBox (mailStr)    'reports the string correctly
  33.     Else
  34.         MsgBox ("Empty!")
  35.     End If
  36.     rstEmail.Close
  37.  
  38.     'now try to get Outlook to co-operate!
  39.     With ctl
  40.         .HyperlinkAddress = "mailto:" & mailStr & "#"
  41.          'new message opens, 
  42.          'but mailStr is cut short part way through an address
  43.     End With
  44. End Sub
  45.  
Jan 13 '10 #9

P: 52
I've, too, been trying to figure out how to do this for quite a while! I'm excited by that last bit of code you wrote julietbrown, but did you ever get it to work properly? If I'm going to spend the time to implement it, I'd like an idea of the outcome first! (yeah, I'm lazy like that...)

Thanks! : )
Feb 13 '10 #10

P: 99
Hello there! This is a fantastic site, but I fear I won't live up to your expectations this time.

I actually decided this whole strategy was bonkers. I have a contact list of about 500 people, and clearly can't expect to get that string into one mail message header.

I changed the solution completely. What happens now is that the e-mail addresses and subject line are exported to an excel spreadsheet: then the user does an "e-mail mail merge" in Word. This is a more flexible solution, and allows the user to edit individual message if she wants to.

Not v helpful, I'm afraid
Good luck
Feb 13 '10 #11

P: 52
That's OK - thanks for answering! : )

I have, at most, 50 emails. After about how many does your code truncate? That is, do I have a chance of making it work as is for my application? I want this to be a single step operation (as opposed to your next solution, which I already do by using Excel and a csv file) as there are MANY other users.

Thanks for any other info you can supply! Cheers! (and, GO CANADA!)
Feb 14 '10 #12

Post your reply

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