Try this code.
It will require reference to Microsoft Outlook. The current code reads the mails from sent folder and saves it to a temp table called tblTmpEmails.
Rajiv
_____________________________________________________________________________________
Public Sub ProcessSentbox()
On Error GoTo err
Dim oOutlook As Outlook.Application
Dim oNs As Outlook.NameSpace
Dim oFldr As Outlook.MAPIFolder
Dim ArchFldr As Outlook.MAPIFolder
Dim oAttachments As Outlook.Attachments
Dim oAttachment As Outlook.Attachment
Dim iMsgCount As Integer
Dim oMessage As Outlook.MailItem
Dim iCtr As Long, iAttachCnt As Long
Dim sFileNames As String
Dim aFileNames() As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim StrSql As String
'get reference to inbox
Set oOutlook = New Outlook.Application
Set oNs = oOutlook.GetNamespace("MAPI")
Set oFldr = oNs.GetDefaultFolder(olFolderSentMail)
Set ArchFldr = Outlook.Session.GetDefaultFolder(olFolderInbox).Folders("Archive")
DoCmd.RunSQL "delete * from tblTmpEmails;"
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblTmpEmails", dbOpenDynaset)
Dim sub1, subj
For Each oMessage In oFldr.Items
With oMessage
'basic info about message
rst.AddNew
' rst.Edit
'.Fields(1).Value = .Importance
Debug.Print
rst.Fields(1).Value = .Subject
If Left(rst.Fields(1).Value, 10) = "Re: ticket" Then
rst!TicketNo = GetTicketNo(rst.Fields(1).Value)
End If
rst.Fields(2).Value = .SenderEmailAddress
rst.Fields(3).Value = .SenderName
rst.Fields(4).Value = .CC
Debug.Print rst.Fields(6).Name
rst.Fields(5).Value = .To
rst.Fields(6).Value = .ReceivedTime
rst.Fields(7).Value = .Body
rst.Fields(8).Value = IIf(.Attachments.Count > 0, True, False)
rst!InorOut = "In"
With oMessage.Attachments
iAttachCnt = .Count
If iAttachCnt > 0 Then
For iCtr = 1 To iAttachCnt
.Item(iCtr).SaveAsFile CurrentProject.Path & "\" & .Item(iCtr).FileName
rst.Fields(9).Value = CurrentProject.Path & "\" & .Item(iCtr).FileName & "|"
Next iCtr
End If
End With
rst.Update
iMsgCount = iMsgCount + 1
End With
DoEvents
' oMessage.Delete
Next oMessage
'Do While oFldr.Items.Count > 0
'For Each oMessage In oFldr.Items
'oMessage.Move ArchFldr
'Next oMessage
'Loop
Set oAttachment = Nothing
Set oAttachments = Nothing
Set oMessage = Nothing
Set oFldr = Nothing
Set oNs = Nothing
Set oOutlook = Nothing
rst.Close
Set rst = Nothing
Exit Sub
err:
MsgBox err.Description
End Sub
_____________________________________________________________________________________
On Wed, Oct 29, 2014 at 11:25 PM, wrmosca@comcast.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Ray
You can send an email from Access using DoCmd.SendObject. I'd build a form that has the To; CC; Subject and body fields with a button to send. That way, you can capture all those fields into a table the same as you would for any form.No need to start in Outlook. And as Jim said you can link to the contacts table so the user can select the address(s) he wants.Regards,Bill Mosca, Founder - MS_Access_ProfessionalsMicrosoft Office Access MVPMy nothing-to-do-with-Access blog
---In MS_Access_Professionals@yahoogroups.com, <rayfrew@gmail.com> wrote :Hi everyone,
I'm not sure if this is possible but...........
What I'd like to do is send an email from Outlook (to a customer) and blind copy the contents along with the recipient's email address into a table in Access. I expect that the table would be simply, PKey, Emailaddress, message. I want to create a history of emails sent to customers - nothing fancy, just the text.
I've successfully created emails that start in Access and use Outlook to send them but can it be done the other way round?
I'm excited about the challenge but as I don't know where to start, I'd appreciate your thoughts and pointers on this. I'm using Access 2010 and Access 2013.
Many thanks
Ray
__._,_.___
Posted by: Rajiv <torajiv@gmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar