copy contents from outlook to excell - please help.

Status
Not open for further replies.

joflo

New Member
Outlook version
Outlook 2013 32 bit
Email Account
Exchange Server
Hi All,

I'm very green with all this vba coding but ive managed to cobble some script together after reading a lot on this forum.

I have some questions.

1. is it possible to enter the current date and time in one of the case pattern elements?
(so that after it fills up all the relevant columns it enters a date stamp in the last column eg "F")
2. is it possible that if the pattern returns a null reading it will use a default text string ie. "no data"
3. how do I gather the information from the email information ie received date, sender, etc.

thanks in advance, loving all the information.

#######code#######

Option Explicit

Sub ACKtoExcel(olItem As Outlook.MailItem)


'Dim olItem As Outlook.MailItem

Dim xlApp As Object

Dim xlWB As Object

Dim xlSheet As Object

Dim vText, vText2, vText3, vText4, vText5 As Variant

Dim sText As String

Dim rCount As Long

Dim bXStarted As Boolean

Dim enviro As String

Dim strPath As String

Dim Reg1 As Object

Dim M1 As Object

Dim M As Object

Dim i As Long

Dim strCell As String



enviro = CStr(Environ("USERPROFILE"))

'the path of the workbook

strPath = enviro & "\Documents\Test123456.xlsx"


On Error Resume Next

Set xlApp = GetObject(, "Excel.Application")

If Err <> 0 Then

Application.StatusBar = "Please wait while Excel source is opened ... "

Set xlApp = CreateObject("Excel.Application")

bXStarted = True

End If

On Error GoTo 0

'Open the workbook to input the data

Set xlWB = xlApp.Workbooks.Open(strPath)

Set xlSheet = xlWB.Sheets("Sheet2")


' use this line if your using and active view

'Set olItem = Application.ActiveExplorer().Selection(1)


'Find the next empty line of the worksheet

rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(xlUp).Row

rCount = rCount + 1


sText = olItem.Body


Set Reg1 = CreateObject("VBScript.RegExp")

For i = 1 To 6


With Reg1

Select Case i

Case 1

.Pattern = "(Incident Number\s*[:](.*))\n"

.Global = False

strCell = ("B")


Case 2

.Pattern = "(Engineer\s*Assigned\s*[:](.*))\n"

.Global = False

strCell = ("C")


Case 3

.Pattern = "(ETA\s*[:](.*))\n"

.Global = False

strCell = ("D")


Case 4

.Pattern = "(Part Assigned\s*[:](.*))\n"



.Global = False

strCell = ("E")


Case 5

.Pattern = "(Serial number\s*[:](.*))\n"

.Global = False

strCell = ("F")


Case 6

.Pattern = "(Part\s*to\s*site\s*via\s*[:](.*))\n"

.Global = False

strCell = ("G")



End Select


End With


If Reg1.Test(sText) Then


' each "(\w*)" and the "(\d)" are assigned a vText variable

Set M1 = Reg1.Execute(sText)

For Each M In M1

vText = Trim(M.SubMatches(1))

'vText2 = Trim(M.SubMatches(2))

'vText3 = Trim(M.SubMatches(3))


xlSheet.Range(strCell & rCount) = vText

'xlSheet.Range(strCell & rCount) = vText2

'xlSheet.Range(strCell & rCount) = vText3



Next

End If

Next i


xlWB.Close 1

If bXStarted Then

xlApp.Quit

End If


Set M = Nothing

Set M1 = Nothing

Set Reg1 = Nothing

Set xlApp = Nothing

Set xlWB = Nothing

Set xlSheet = Nothing

End Sub
 
1. is it possible to enter the current date and time in one of the case pattern elements?
(so that after it fills up all the relevant columns it enters a date stamp in the last column eg "F")
2. is it possible that if the pattern returns a null reading it will use a default text string ie. "no data"
3. how do I gather the information from the email information ie received date, sender, etc.
1. Yes - use Date for the date only or Now for the current date and time.

2. Yes, if you use an if statement something like this:
if vText = "" then
vText = "no data"
end if

3. use field names
olItem.receivedtime
olItem.sendername
olItem.subject
etc. You can get the field names from the VBA Help (F2 when in the VBA editor)
 
thanks for the advice.
Ive tried to put this into the code but I am not making any progress.

sorry im not very good at this.
 
Replace the section that begins and ends with the first and last lines - Next i rolls through the case statements - then when you are done, it grabs the fields from the email.

Code:
 For Each M In M1
vText = Trim(M.SubMatches(1))

If vText = "" Then
vText = "No Data"
End If

xlSheet.Range(strCell & rCount) = vText
Next
End If

Next i

xlSheet.Range("H" & rCount) = Date
xlSheet.Range("I" & rCount) = olItem.ReceivedTime
xlSheet.Range("J" & rCount) = olItem.SenderName
xlSheet.Range("K" & rCount) = olItem.SenderEmailAddress

xlWB.Close 1
 
When you put it like that, it almost seems obvious...

I cannot thank you enough.

Thank you
Thank you
Thank you
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
C Copy from one Profile to another Using Outlook 0
M "Attachment Detacher for Outlook" add in, does it update the server copy of the email? Using Outlook 1
C Outlook 365 Copy/Save Emails in Folder Outside Outlook to Show Date Sender Recipient Subject in Header Using Outlook 0
D Copy Appointment Body to Task Body Outlook VBA and Custom Forms 0
M copy field value to custom field Outlook VBA and Custom Forms 0
O In Agenda-view - How to copy an existing item months ahead or back? Using Outlook 0
C Move or copy from field to field Outlook VBA and Custom Forms 0
Z Copy specific email body text Outlook VBA and Custom Forms 0
B Need to Copy an email to a subfolder Outlook VBA and Custom Forms 2
O Outlook 365 - How to create / copy a new contact from an existing one? Using Outlook 5
S Copy Tasks/Reminders from Shared Mailbox to Personal Tasks/Reminders Outlook VBA and Custom Forms 0
A Cannot copy this folder because it may contain private items Using Outlook 0
C Copy Move item won't work Outlook VBA and Custom Forms 2
Z VBA to convert email to task, insert text of email in task notes, and attach copy of original email Outlook VBA and Custom Forms 4
Commodore Move turns into "copy" Using Outlook 3
C Copy Outlook contact field value to another field Outlook VBA and Custom Forms 1
J Copy to calendar function no longer working in outlook 365 Using Outlook 5
F Copy and replace not update contact in another pst Using Outlook 0
B Outlook Business Contact Manager with SQL to Excel, User Defined Fields in BCM don't sync in SQL. Can I use VBA code to copy 1 field to another? BCM (Business Contact Manager) 0
Commodore Folders always closed in move/copy items dialog box Using Outlook 3
N Outlook rules don't create a copy for bcc'ed emails Using Outlook 3
geofferyh Outlook 2010 How to Copy Outlook Attachment to a Specific Folder? Outlook VBA and Custom Forms 3
S Custom Form, copy user field data to message body Outlook VBA and Custom Forms 12
R Copy Outlook Public Folders to a File Server Shared Folder Using Outlook 0
K Outlook Rules: Move a Copy Using Outlook 4
oliv- HOW TO COPY /USE FOLDERS ICONS Outlook VBA and Custom Forms 2
E Copy e-mail body from outlook and insert into excel Outlook VBA and Custom Forms 3
B Copy/Move Exchange inbox to Pop inbox Using Outlook 4
R Sending email copy (*.msg file) of sent email if subject line contains specific string. Outlook VBA and Custom Forms 1
O Copy mails from many subfolders to 1 foldr Using Outlook 2
K ind specific Subject line from outlook and copy the content of the email body to exce Outlook VBA and Custom Forms 0
K How to find specific header and copy the mail body Using Outlook 0
J Copy or Export Outlook Mail to Excel Outlook VBA and Custom Forms 6
G Copy Contact field to Appointment Custom Form Field Outlook VBA and Custom Forms 2
G How to Copy Multi Select Listbox Data to Appointment Outlook VBA and Custom Forms 3
Carrie Dickey Outlook 2016 created two calendars titled Calendar1 - appear to be a copy Using Outlook 2
P How to copy and append data from Outlook 2016 message into Excel 2016 workbook Using Outlook 0
Stilgar Relsik Create a rule to copy text from an email and paste it in the subject line. Using Outlook 1
R Macro to copy email to excel - Runtime Error 91 Object Variable Not Set Outlook VBA and Custom Forms 11
H Macro to Copy Specific content from Mail Body and Paste to Excel Outlook VBA and Custom Forms 4
M How to keep reccurence during copy tasks to calendar? Using Outlook 1
Diane Poremsky Copy New Appointments to Another Calendar using VBA Using Outlook 0
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook Using Outlook 0
C Copy Task to Non-Microsoft PIM "Rainlendar" Using Outlook 0
G VBA Copy draft email to a new email - attachments not copided Using Outlook 7
C Copy email to excel runtime error 5020 Using Outlook 5
I Copy email from folder to folder - FAILS Using Outlook 5
M Copy new appointments created in multiple shared calendars to another exchange calendar Outlook VBA and Custom Forms 1
Q Why can't I copy image with embedded hyperlink from email to Word Using Outlook 0
I How to make a copy of a task Using Outlook 8

Similar threads

Back
Top