ACCESS FEED: Re: [MS_AccessPros] Outlook alert with auto email

Kamis, 01 Januari 2015

Re: [MS_AccessPros] Outlook alert with auto email

Just in case, depending on the requirements, I used an Outlook *.oft template within which I had some database fields that I replaced via the code. I've scrubbed the Outlook template and the code a bit to give you the general idea. I've had no issues with this. However, this isn't designed to handle 40-12 messages at once. It's intended to allow the user to send messages to one location at a time although the actual contents can be a reference to any number of issues or people.

Maybe this will help.

Jeff

~~~ Here's an example of the *.oft file contants. ~~~

%Location%

%addr1%

%addr2%

Dear %Location%,

Lots of pithy pontifications....

%Persons%

Thank you for your cooperation,

%sender%

~~~ end of *.oft file example ~~~

~~~ Code Example ~~~
Function UseOutlookTmplt(strFile, strName, strUserName)

Dim strlocTbl As String
Dim strRecipients As String
Dim strQuery As String
Dim strlocTblTo As String
Dim strlocTblEmail As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim olApp As Outlook.Application
Dim olAccounts As Outlook.Accounts
Dim olAccount As Outlook.Account
Dim olAccountTemp As Outlook.Account
Dim olMail As MailItem

'    strUserName = GetUserFullName
    strQuery = "SELECT tlkp_Lookup_locTbl_Contacts.Place, tlkp_Lookup_locTbl_Contacts.Representative, tlkp_Lookup_locTbl_Contacts.Administrative"
    strQuery = strQuery & " FROM tlkp_Lookup_locTbl_Contacts WHERE (((tlkp_Lookup_locTbl_Contacts.Place)='" & strlocTbl & "'));"

    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset(strQuery) '("SELECT zref_NCCountyCodes.CountyEmail FROM zref_NCCountyCodes WHERE (((zref_NCCountyCodes.CountyName)=" & """" & locPlace & """" & "));")
    strlocTblTo = rs!Representative
    strlocTblEmail = rs!Administrative
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    Set olApp = CreateObject("Outlook.Application")

     'Get email account based on from email address
    Set olAccounts = olApp.Application.Session.Accounts

    For Each olAccountTemp In olAccounts
        Set olAccount = olAccountTemp
    Next
''   Use the html new line of <br> to force a new line in a message
'strName = strName & "    " & "<br>" & strName & "    " & "<br>" & "doofratz"
'
    Set olMail = olApp.CreateItemFromTemplate(strFile) ' <- *.oft template file
    olMail.Display
    With olMail   'With MailSendItem
        .Subject = "Request for Information"

' add county/locTbl field doofratz ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        .To = Forms![frm_locTbl]![locPlace] & ".boe@ncsbe.gov"
        .SendUsingAccount = olAccount
        .Display
        .HTMLBody = Replace(olMail.HTMLBody, "%Location%", Forms![frm_locTbl]![locPlace])
        strRecipients = "Person: " & strName & "<br>" & "ID: " & Forms![frm_locTbl]![ID_Number]
        .HTMLBody = Replace(olMail.HTMLBody, "%Persons%", strRecipients)
        .HTMLBody = Replace(olMail.HTMLBody, "%sender%", strUserName)
     End With

    UseOutlookTmplt = "Outlook~"

Exit_UseOutlookTmplt:
    Set olMail = Nothing
    Set olAccounts = Nothing
    Set olAccount = Nothing
    Set olApp = Nothing
    Exit Function

End Function        '   end UseOutlookTmplt
~~~ end of code example ~~~

From: "'Bill Mosca' wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, January 1, 2015 1:09:14 PM
Subject: RE: [MS_AccessPros] Outlook alert with auto email

Patty

This has been going on since Outlook 2000. I'm surprised it hasn't happened to you before this. Or it could be an update for Office changed something. There are a few work-arounds such as ClickYes and Outlook Redemption (both links found on our More>Links>Development tools section), but the one I find to work flawlessly even when your user's Outlook is not the same version as Access. It's call vbMAPI and is available at everythingaccess.com. Unfortunately, it is not free, but the online help, sample code and tech support is excellent. And you only have to buy one license as a developer. Users do not need a license.

Regards,
Bill Mosca,
Founder, MS_Access_Professionals
That'll do IT http://thatlldoit.com
MS Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852

My Nothing-to-do-with Access blog

http://wrmosca.wordpress.com

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, December 30, 2014 8:13 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Outlook alert with auto email

I have an application that sends automated emails through outlook. I don't use the DoCmd option but rather a version of:

Dim outl As outlook.Application
Set outl = New outlook.Application
Dim mi As outlook.MailItem
Set mi = outl.CreateItem(olMailItem)
mi.Body = "test message"
mi.Subject = "message from access"
mi.To = ""
mi.Send
Set mi = Nothing
Set outl = Nothing

This has worked flawlessly for me over time, but today outlook is suddenly throwing alerts, one per email, of course.

I cannot confirm any changes to virus software, and the alert is clearly from outlook.

Has anyone else seen this or not what is causing it?

Thank you,

Patty

Patricia Mapes

---

Jeffrey Park Jones
Excel, Access, Word, Office Expert
Excel and Access, LLC®
http://ExcelAndAccess.Com

919-671-9870
5109 Deer Lake Trail
Wake Forest, NC 27587
jpjones23@centurylink.net

__._,_.___

Posted by: jpjones23@centurylink.net

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

Visit Your Group