Hyperlink Follow to send e-mail from Access 2007

[julietbrown]

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!

'address is a field containing an e-mail address

Private Sub Send_Click()
    Dim ctl As CommandButton
    Set ctl = Me.Send
    With ctl
        .HyperlinkAddress = "mailto:" & address & "#"
    End With
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.

[MMcCarthy]

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

[ADezii]

Try

Private Sub Send_Click()
With Me![Send]
  .HyperlinkAddress = "mailto:" & Address
  .Hyperlink.Follow
End With
End Sub

Or

Private Sub Send_Click()
Application.FollowHyperlink "mailto:" & Address
End Sub

[julietbrown]

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?

[julietbrown]

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.

[MMcCarthy]

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

[julietbrown]

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!

[NeoPa]

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.

[julietbrown]

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

Private Sub SendGroupEmail_Click()
    Dim ctl As CommandButton
    Set ctl = Me.SendGroupEmail

    'declare a string to hold the addresses
    Dim mailStr As String
    mailStr = ""

    'declare and open a recordset for the table containing the addresses
    Dim rstEmail As Recordset
    Dim db As Database
    Set db = CurrentDb
    Set rstEmail = db.OpenRecordset("EMAILING_TABLE", dbOpenDynaset)
    If Not (rstEmail.EOF And rstEmail.BOF) Then
        rstEmail.MoveFirst
        'loop thro the recordset, appending each e-mail to the mailStr
        While Not (rstEmail.EOF)
            If Not (rstEmail!EmailAddress = "") Then
                mailStr = mailStr & rstEmail!EmailAddress & ";"
            End If
            If Not rstEmail.EOF Then
                rstEmail.MoveNext
            End If
        Wend
        If Not (mailStr = "") Then
            'knock the final semi-colon off the end of the mailStr
            mailStr = Left(mailStr, Len(mailStr) - 1)
        End If
    End If
    'debug messages
    If Not (mailStr = "") Then
        MsgBox (mailStr)    'reports the string correctly
    Else
        MsgBox ("Empty!")
    End If
    rstEmail.Close
    
    'now try to get Outlook to co-operate!
    With ctl
        .HyperlinkAddress = "mailto:" & mailStr & "#"
         'new message opens, 
         'but mailStr is cut short part way through an address
    End With
End Sub

[Mmmel]

I'm right with ya!

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

[julietbrown]

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

[Mmmel]

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