How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask R. Michael Your Own Question

R. Michael
R. Michael, MIS Analyst
Category: Homework
Satisfied Customers: 59
Experience:  Build Reports and Database Administration. MS Office and VBA are Primary skills. I also work in PHP, JavaScript, HTML, MySQL.
71693728
Type Your Homework Question Here...
R. Michael is online now
A new question is answered every 9 seconds

I have the following VBA task to complete. Your task is to

Customer Question

I have the following VBA task to complete.
Your task is to create a macro in Microsoft Outlook using VBA that, on a button click on the Outlook screen, sends details about the currently selected emails into an Excel spreadsheet. This will include the sender’s name, email address and subject. Note that the macro should be able to handle multiple selections of emails, not just one.
As an optional extension, try to implement as many of these features as possible:
- An automatic sort on the Excel document based on one of the imported fields
- An Excel field that’s dynamically populated based on information from one of the imported fields. An example would be the sender’s company based on the email address’s ending
- Automatically moving the selected emails into a separate Outlook folder after they’ve been documented in Excel
- Automatically highlighting missing fields in Excel, e.g. A missing subject
So far, I have only worked out some code and I am stucked. I am looking for an answer for the task above and explanations. The code I have worked out so far is as below.
Sub mail_to_excel2()
'select info from all mails in inbox
Dim nmsname
Dim infolder
Dim omail As Outlook.MailItem
Dim sender
Dim subject
Dim email
Set nmsname = Outlook.Application.GetNamespace("MAPI")
Set infolder = nmsname.GetDefaultFolder(Outlook.olFolderInbox)
For Each omail In infolder.Items
If omail.Class = 43 Then
sender = omail.SenderName
subject = omail.subject
email = omail.SenderEmailAddress
End If
Next
'open excel
Dim xlApp As Object
Dim sourceWB
Dim sourceWS
Dim strfile As String
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.EnableEvents = True
End With
strfile = "C:\Email\mail.xls"
Set sourceWB = xlApp.Workbooks.Open(strfile, , False, , , , , , , True)
Set sourceWS = sourceWB.worksheets(1)
sourceWB.Activate
' update worksheet
Dim i As Integer ' row number in excel sheet
Dim count As Integer ' number of emails selected in outlook
i = 1
For count = 1 To infolder.Items.count
'If condition to import mails received in last 20 days
'To import all emails, comment or remove this IF condition
If VBA.DateValue(VBA.Now) - VBA.DateValue(infolder.Items.Item(irow).ReceivedTime) <= 20 Then
i = i + 1
sourceWS.Cells(i, 1).Select
sourceWS.Cells(i, 1).Value = infolder.Items.Item(irow).SenderName
sourceWS.Cells(i, 2).Value = infolder.Items.Item(irow).subject
sourceWS.Cells(i, 3).Value = infolder.Items.Item(irow).SenderEmailAddress
End If
Next irow
MsgBox "Outlook Mails Extracted to Excel"
' Set Folder = Nothing
' Set sFolders = Nothing
End Sub
Submitted: 1 year ago.
Category: Homework
Expert:  R. Michael replied 1 year ago.
GI,
THANK YOU FOR CHOOSING JUSTANSWER.COM! MY NAME IS***** , I HAVE EXTENSIVE EXPERIENCE WITH VBA ESPECIALLY INTEGRATION OF OUTLOOK AND EXCEL. I WOULD BE HAPPY TO ASSIST YOU WIT THIS REQUEST.
Expert:  R. Michael replied 1 year ago.
THIS ANSWER IS LOCKED!

You need to spend $3 to view this post. Add Funds to your account and buy credits.