I have a similar query... Does anyone know how i can copy some lements of th message into an Excel file from Outlook and validate it???
I have a custom Outlook form which is sent companywide. The users fill in their holiday requests and get it authorised by their team leaders. I would ideally like to import the data from this form into an excel spreadsheet so that reports and analysis can be made.
Once the data is in Excel I will be fine manipulating it how I want, the problem is getting it there. Can anyone help/suggest a method of how I would go about storing the results from the form and then exporting all fields to a row on a spreadsheet?
well you can capture the body, sendername, senderemail etc... by setting them to variable...
however the tricky part is then getting them into excel... there is an answer...
took me awhile for my project
make sure you have the refences (in VBE tools>> references then find MS Excel)
here is detect excel codeCode:'Dim vfiles As FileDialog Dim oExcelObjectRefApp As Excel.Application Dim oExcelObjectRefBook As Excel.Workbook Dim oExcelObjectRefSheet As Excel.Worksheet Dim oExcelObjectRefBook1 As Workbook Dim oExcelObjectRefSheet1 As Worksheet Dim oExcelObjectRefRange As Object 'Range Dim oExcelObjectRef As Object ' Variable to hold reference ' to Microsoft Excel. Dim ExcelWasNotRunning As Boolean Dim dlg As Office.FileDialog This will get excel if not running, if it is running may be an error?? Set oExcelObjectRef = VBA.GetObject("", "EXCEL.APPLICATION") 'ensure excel is running??? DetectExcel
here i think you need this in your declarationsCode:Sub DetectExcel() ' Procedure dectects a running Excel and registers it. Const WM_USER = 1024 Dim hWnd As Long ' If Excel is running this API call returns its handle. hWnd = FindWindow("XLMAIN", 0) If hWnd = 0 Then ' 0 means Excel not running. Exit Sub Else ' Excel is running so use the SendMessage API ' function to enter it in the Running Object Table. SendMessage hWnd, WM_USER + 18, 0, 0 End If End Sub
then open the file, fill it, save it, close itCode:Declare necessary API routines: Declare Function FindWindow Lib "user32" Alias _ "FindWindowA" (ByVal lpClassName As String, _ ByVal lpWindowName As Long) As Long Declare Function SendMessage Lib "user32" Alias _ "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _ ByVal wParam As Long, _ ByVal lParam As Long) As Long
I dun really quite understand how to implement it. I need to have a simliar application.
Meaning, i want to copy all the emails send to me in outlook and export the Subject and Body to a Excel Workbook.
Can you give a little bit more guidance ?
I have searched and searched this kind of thing and I am also stuck.
I need to create a form on outlook and then when data has been inputted on the form on outlook...this then queries an excel sheet....
This is to be used for staff to request annual leave...and for it to be granted / declined if the space being requested is available or not.
I am willing to pay a little for a solution..or donate a set amount to their charity..
Can anyone help?..or offer a solutiong
1st. you have to have excel reference library... In VBE go to tools, reference's, and find microsoft excel 10 object library... (may be on your computers 8,9,10).
when you open excel in the code you find your range or set it and then you set your subject, body to a variable and then set your range in excel to equal that variable...
then set up excelCode:With oMessage.Attachments iAttchCount = .Count Set oAttachment = oMessage.Attachments If iAttchCount > 0 Then For iCtr = 1 To iAttchCount sEmailSenderEmail = oMessage.SenderEmailAddress sEmailSenderName = oMessage.SenderName dRecvdTime = oMessage.ReceivedTime sEmailSubject = oMessage.Subject sEmailAttachName = .Item(iCtr).FileName 'So I know where it's saved to 'Wait this isn't saving it that is above so conBVCW_EVAL_FOLDER may need to change sEmailFileSavedTo = conBVCW_EVAL_FOLDER & sEmailAttachName
And thenCode:Set oExcelObjectRefBook = oExcelObjectRef Set oExcelObjectRefSheet1 = oExcelObjectRefBook.Worksheets("Workbook Info")
Don't forget the code I posted early... if you need more help let me know... best thing is to get is far as you can... then you can email me the module and I can help from there...Code:oExcelObjectRefSheet1.Range("A11") = sEmailSubject
There are currently 1 users browsing this thread. (0 members and 1 guests)