Choosing email server in Access

[Leathem]

I am using Access to send emails, but the computer I am using has more than one email client (specifically Firefox and Microsoft Outlook). Normally Firefox is my default client, but I would like to have Access use Outlook because the third-party application ClickYes works only with it. My solution of the moment is to temporarily set Outlook as the default client, but it would be much smoother if I could leave Firefox as the default and have Access direct the emails to Outlook. Is this possible?
(FYI, I posted the VBL code that I use for sending emails in this forum on the 19th of January.)

Leathem

 

[joeKra]

Hello,

you can install a reference to outlook and attach code to a button to send your emails,

for more information follow this link
jephens.com/2007/05/13/how-to-send-e-mail-from-ms-access-using-outlook/[/url]

 

[Leathem]

Hi Joe,
First, thanks for the quick reply and the code. I've already incorporated the tracking code - a great idea I hadn't thought of but will find very useful.

Forgive a novice VBL user, but I'm trying to reconcile your code with mine. As I understand it you create an Outlook object with the statement

Set Mymail = MyOutlook.CreateItem(olMailItem)

after which you build and then send the emails.
I am using the code

DoCmd.SendObject acReport, "Enrolled Students Letter", "PDFFormat(*.pdf)", _
           EmailAddress, "", "", "CLS Course Enrollment confirmation", "Attached please find your confirmation letter. It is in Adobe pdf format.", False, ""

which all in one statement builds the report to be attached, fills in the email address and subject line and then attaches the report and sends the email.

Is there a (relatively easy) way to direct my DoCmd statement to use Outlook rather than the default client? It doesn't seem to be an available option within the command. Otherwise I can go on using the klugy method of externally setting and re-setting the default client.

 

[joeKra]

i am not sure i understand your question...
Do you want to use sendobject method together with the code provided?? i dont believe it should work its a different method, though... you have to choose which way to send the email whether sendobject method or the user defined function provided

 

[joeKra]

First you have to save the file on your hard drive


Dim FilName As String
FilPath= "c:\& me.name"

DoCmd.OutputTo acOutputReport, "Enrolled Students Letter", acFormatPDF, FilPath

i tried to make it a little simpler for you, but i highly recommend you to go over the code and explore all the options to get a better clue

in the code notice that the attachment code is commented so uncomment it

MyMail.Attachments.Add FilPath, olByValue, 1, "My Displayname"

on this line make the email word for a variable
MyMail.To = MailList(email)

change the Subjectline$ to Subjectline$ = mySubject

SendEMail(FilPath as string,email as string,mySubject as string)

i am just thinking if it should rather be converted to a sub instead of a function

 

[Leathem]

I'm working on it...
But I think I see a complication. If I correctly understand your original code you create an email (with an optional attachment) and then send that email to all recipients in a list. My situation is a bit different. The attachment letter for each recipient is different, so the code has to create the attachment for each recipient within the loop as well as sending the email. Thus, outputting the attachment to hard drive using a fixed path won't work; I'd have to modify the filename for each attachment. I'm sure that's do-able, though I need to think on this some more.

 

[joeKra]

From where do you attach the letter is it a filtered report ,a value from a field or just a file from your hard drive please explain more your situation?

 

[Leathem]

It's a report. Perhaps it would help if I include the full code:

Private Sub Command20_Click()
' Error handler
    On Error GoTo 0
    ' Create a SELECT command
    Dim StudentQuery
    StudentQuery = "SELECT [StudentDataQuery].StudentName, [StudentDataQuery].[E-MAIL] FROM [StudentDataQuery]"
    ' open the table to record emails sent
    Dim EmailRecordTable As Recordset
    Set EmailRecordTable = CurrentDb.OpenRecordset("EmailRecord")
    ' Get a recordset using the query
    Dim Recordset
    Set Recordset = CurrentDb.OpenRecordset(StudentQuery)
    ' Move through the recordset looking at each record
    Do Until Recordset.EOF
        Dim StudentName, EmailAddress
        StudentName = Recordset("StudentName")
        EmailAddress = Recordset("E-MAIL")
        Forms.DistributeLetters.StudentName = StudentName
        Forms.DistributeLetters.EmailAddress = EmailAddress
        If EmailAddress = "n/a" Or IsNull(EmailAddress) Then
            Forms.DistributeLetters.EmailAddress = "No Email"
            Else
            DoCmd.SendObject acReport, "Enrolled Students Letter", "PDFFormat(*.pdf)", _
           EmailAddress, "", "", "CLS Course Enrollment confirmation", "Attached please find your confirmation letter. It is in Adobe pdf format.", False, ""
            EmailRecordTable.AddNew
            EmailRecordTable("StudentName") = Forms.DistributeLetters.StudentName
            EmailRecordTable("EmailAddress") = Forms.DistributeLetters.EmailAddress
            EmailRecordTable("DateSent") = Now()
            EmailRecordTable.Update
        End If
        Recordset.MoveNext
        Loop

End Sub