Solved

Replying to an Outlook email from Access 7

Posted on 2012-04-09
4
329 Views
Last Modified: 2012-04-12
I have been using Outlook automation to send Outlook email from an Access 7 database, which is essentially a contact and project manager. I also link to an Outlook InBox, import new email into the database. I then run a query which creates a new table in which each email has a unique identifier. I can then assign each email to a relevant project and related work group members.  So for each project,  I end up with a pretty decent record of email that I have sent and received, and can create new email without leaving the project screen. Everything works well except I don't have a way to reply to an email from within Access.  I am thinking of adding a "Reply" button which would work like the "Send Email" code included below, except that it would copy the contents of the current email into a new form, and add something like "On <DATE> you wrote"  and bring to the top of the form to create my new message. I would then use the "Send Email" button or something like it. Before trying to build this though, I am wondering if anyone has a better way, or can maybe point me to some existing code.

 


Private Sub Sendemail_Click()

   Dim stDocName As String
   Dim stLinkCriteria As String
If Not IsNull(FaceID) Then
   stDocName = "frmFaces"
   stLinkCriteria = "FaceID=" & Forms![frmMaster2]![frmTransactionSubform].Form![With Whom]
   
   DoCmd.OpenForm stDocName, , , stLinkCriteria
 
End If
   
     DoCmd.OpenForm "frmFaces"
    Dim t As String
    t = Forms![frmFaces].EMAIL
    DoCmd.Close
    DoCmd.OpenForm "frmMaster2"
         
Dim MyOutlook As Outlook.Application
Dim MyEmail As Outlook.MailItem
 
Set MyOutlook = CreateObject("Outlook.application")
Set MyEmail = MyOutlook.CreateItem(olMailItem)

MyEmail.To = t
MyEmail.Subject = Forms![frmMaster2].Form![Headline]
MyEmail.body = Forms![frmMaster2]![frmTransactionSubform].Form![Notes] & vbCrLf & GetSignature("PR")
 
MyEmail.display

Dim Clip As String
Dim textonly As String

Clip = Forms![frmMaster2].Form![Headline] 'make the line shorter for coding purposes
textonly = InStr(Clip, "#")
Clip = Replace(Clip, "#", "") 'remove the #s
MyEmail.Subject = Mid(Clip, 1, textonly - 1)
   
End Sub

   Function GetSignature(strSignatureName As String) As String
    Const ForReading = 1
    Const TriStateTrue = -1
    Dim objFSO As Object, objFile As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(Environ("USERPROFILE") & "\Application Data\Microsoft\Signatures\" & strSignatureName & ".txt", ForReading, False, TriStateTrue)
    GetSignature = objFile.ReadAll
    objFile.Close
    Set objFile = Nothing
    Set objFSO = Nothing
   

End Function
0
Comment
Question by:jnero
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37826317
Too much is involved to do something like this in Access, let alone Access 7... IMHO...
Why not just reply from within outlook and avoid re-inventing the wheel?

Let's see what other experts may think...
0
 

Author Comment

by:jnero
ID: 37827429
Thanks.  I am trying to maintain a continuous workflow, eliminating white space between applications, which can increase productivity and cut costs significantly, especially if I can extend to a work group. Part of the benefit is the streamlining the activity, another part is avoidance of the distractions that invariably come when you go into another application, such as dealing with new email that has arrived. Yes it is a matter of discipline and concentration, but, at least at the team level, it would be easier to automate the process than enforce work habits for each member. But you may be right that this may be beyond the scope of Access 7 -- which kind of sends me back to the drawing board.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37827857
As with anything...
If you put enough work into it, you can do anything you want...
How much trouble you are willing to go through is another story though...
;-)

That being said your approach seems like it would be the best technique...
<Before trying to build this though, I am wondering if anyone has a better way, or can maybe point me to some existing code.>

JeffCoachman
0
 

Author Closing Comment

by:jnero
ID: 37840352
It was good to see that someone else agreed with my general approach while providing a reality check that I may be overreaching with Access 7. This was the last peg in my application and with that, I will see where I hit the wall in the next rev, which will likely be built on something else, but probably not by me.
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question