?
Solved

Replying to an Outlook email from Access 7

Posted on 2012-04-09
4
Medium Priority
?
334 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
  • 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 1500 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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

755 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