I am in the beginning stages of an attempt to capture data from Outlook e-mail messages into an Access database. There's probably a better way, but my first idea is to copy/paste the message body into Excel, then match cells to fields. There is enough consistency in the e-mail formatting to do this. One piece of data I need is a zip code. Unfortunately, it ends up in the same cell as the rest of the address info. I can isolate it to an adjacent cell with this nice formula that I found searching this forum.But, the formula throws an error when I try to use it in the Access vba. I am hoping it's a simple syntax tweak and that someone can help me correct it. Thanks very much!
=Trim(Right(Substitute(B31, ",", Rept(" ", Len(B31))), Len(B31)))
Private Sub cmdNewFromEmail_Click() Dim xl As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim filePath As String Dim strWhere As String Set xl = New Excel.Application filePath = "S:\Employee\" & "Excel Test File" & ".xlsx" Set xlBook = xl.Workbooks.Open(filePath) Set xlSheet = xlBook.Worksheets(1) xl.Visible = True DoCmd.GoToRecord , , acNewRec Me.File_Number = Nz(DMax("File_Number", "ClaimInfo1", strWhere), 0) + 1 Me.Invoice_Number = Me.File_Number & "-01" DoCmd.RunCommand acCmdSaveRecord Me.SubformContainer.SourceObject = "Appraisals_Subform2" 'Binds Client Billing tab to Invoicing form when setting up new file Forms!Invoicing_Form.TabCtlEval = 0 'Sets focus on the appropriate tab. With xlSheet .Range("C31").Formula = "=Trim(Right(Substitute(B31, ",", Rept(" ", Len(B31))), Len(B31)))" End With Me.Claim_Number = Range("B2") Me.Vehicle_Owner = Range("B44") Me.cboAdjusterName = Range("B16") Me.[Date Of Loss] = Range("B8") End Sub