none
VBA code for opening Excel 2010 file in Outlook RRS feed

  • Question

  • Hello,

    I have a macro in Excel 2010 which lets me save an open file to the company SharePoint site under the prior business days date which works great. I receive the file daily, open the email then run the macro. What I'd like to do is update the macro so it opens the daily email then runs the rest of the code. Ive seen some lengthy examples online which help you open an email then send it out, I simply want to open the email and save the file.

    Dim PrevBusDay As Date
    Sub Site84SaveFileToSP()

    Dim PrevBusDay As Date

    PrevBusDay = Date - 1

    Select Case Weekday(PrevBusDay)
        Case vbSunday
            PrevBusDay = PrevBusDay - 2
        Case vbSaturday
            PrevBusDay = PrevBusDay - 1
    End Select

     

    ActiveWorkbook.SaveAs FileName:= _
            "https://one.companyname.net/sites/CarrierHandlingDatabase/Direct Bill CMS/Site 84 Cash Receipts/Site 84 Cash Receipts Report " & Format(PrevBusDay, "mmddyy") & ".xlsx" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False


    Lorac1969

    Monday, May 11, 2015 2:39 PM

Answers

  • >> What I'd like to do is update the macro so it opens the daily email then runs the rest of the code

    Based on my understanding, you are going to open a mail Item in Excel. You could call a function which display a mail Item In your sub. The following code could open a mail Item in Excel that you can refer to. And the sample open a mail item received today, you may make your own rule to show the mail item.

    Function ShowMailItem()
    
    Dim olApp As Outlook.Application
    
    Dim objNS As Outlook.Namespace
    
    Dim olFolder As Outlook.MAPIFolder
    
    Dim msg As Outlook.MailItem
    
    Set olApp = Outlook.Application
    
    Set objNS = olApp.GetNamespace("MAPI")
    
    Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
    
    Set olFolder = olFolder.Folders("TestFolder") 'mail folder
    
    ' made your own rules to show the mail Item.
    
    For Each msg In olFolder.Items
    
       If DateValue(msg.ReceivedTime) = Date Then
    
      msg.Display    'show the mail item
    
       End If
    
    Next

    End Function

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, May 12, 2015 4:39 AM
    Moderator

All replies

  • Hello,

    It looks like you need to automate Outlook from your Excel VBA code. The How to automate Outlook from another program article describes all the required steps to get started quickly. 

    Monday, May 11, 2015 4:05 PM
  • >> What I'd like to do is update the macro so it opens the daily email then runs the rest of the code

    Based on my understanding, you are going to open a mail Item in Excel. You could call a function which display a mail Item In your sub. The following code could open a mail Item in Excel that you can refer to. And the sample open a mail item received today, you may make your own rule to show the mail item.

    Function ShowMailItem()
    
    Dim olApp As Outlook.Application
    
    Dim objNS As Outlook.Namespace
    
    Dim olFolder As Outlook.MAPIFolder
    
    Dim msg As Outlook.MailItem
    
    Set olApp = Outlook.Application
    
    Set objNS = olApp.GetNamespace("MAPI")
    
    Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
    
    Set olFolder = olFolder.Folders("TestFolder") 'mail folder
    
    ' made your own rules to show the mail Item.
    
    For Each msg In olFolder.Items
    
       If DateValue(msg.ReceivedTime) = Date Then
    
      msg.Display    'show the mail item
    
       End If
    
    Next

    End Function

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, May 12, 2015 4:39 AM
    Moderator