Launch Outlook to compose a message with subject and attachment by Outlook’s command line switches

I can give you the complete code in one go but that wouldn't help you learn from it ;) So let's Break up your requests and then we will tackle them 1 by 1. This is gonna be a very long post so be patient :)

There are total 5 parts which will cover all 7 (yes 7 and not 6) points so you don't have to create a new question for your 7th point.

PART - 1

1. Creating a Connection to Outlook
2. Checking if there is any unread email
3. Retrieving details like Sender email Address, Date received, Date Sent, Subject, The message of the email

See this code example. I am latebinding with Outlook from Excel then checking if there are any unread items and if there are I am retrieving the relevant details.

Const olFolderInbox As Integer = 6

Sub ExtractFirstUnreadEmailDetails()
    Dim oOlAp As Object, oOlns As Object, oOlInb As Object
    Dim oOlItm As Object

    '~~> Outlook Variables for email
    Dim eSender As String, dtRecvd As String, dtSent As String
    Dim sSubj As String, sMsg As String

    '~~> Get Outlook instance
    Set oOlAp = GetObject(, "Outlook.application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

    '~~> Check if there are any actual unread emails
    If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "NO Unread Email In Inbox"
        Exit Sub
    End If

    '~~> Store the relevant info in the variables
    For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
        eSender = oOlItm.SenderEmailAddress
        dtRecvd = oOlItm.ReceivedTime
        dtSent = oOlItm.CreationTime
        sSubj = oOlItm.Subject
        sMsg = oOlItm.Body
        Exit For
    Next

    Debug.Print eSender
    Debug.Print dtRecvd
    Debug.Print dtSent
    Debug.Print sSubj
    Debug.Print sMsg
End Sub

So that take care of your request which talks about storing details in the variables.

PART - 2

Now moving on to your next request

1. Download the one and only attachment from the first email (the newest email) in my Outlook inbox
2. Save the attachment in a file with a specified path (eg: "C:...")
3. Rename the attachment name with the: current date + previous file name

See this code example. I am again latebinding with Outlook from Excel then checking if there are any unread items and if there are I am further checking if it has an attachment and if it has then download it to the relevant folder.

Const olFolderInbox As Integer = 6
'~~> Path for the attachment
Const AttachmentPath As String = "C:\"

Sub DownloadAttachmentFirstUnreadEmail()
    Dim oOlAp As Object, oOlns As Object, oOlInb As Object
    Dim oOlItm As Object, oOlAtch As Object

    '~~> New File Name for the attachment
    Dim NewFileName As String
    NewFileName = AttachmentPath & Format(Date, "DD-MM-YYYY") & "-"

    '~~> Get Outlook instance
    Set oOlAp = GetObject(, "Outlook.application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

    '~~> Check if there are any actual unread emails
    If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "NO Unread Email In Inbox"
        Exit Sub
    End If

    '~~> Extract the attachment from the 1st unread email
    For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
        '~~> Check if the email actually has an attachment
        If oOlItm.Attachments.Count <> 0 Then
            For Each oOlAtch In oOlItm.Attachments
                '~~> Download the attachment
                oOlAtch.SaveAsFile NewFileName & oOlAtch.Filename
                Exit For
            Next
        Else
            MsgBox "The First item doesn't have an attachment"
        End If
        Exit For
    Next
 End Sub

PART - 3

Moving on to your next request

1. Save the email into a different folder with a path like "C:..."

See this code example. This save the email to say C:\

Const olFolderInbox As Integer = 6
'~~> Path + Filename of the email for saving
Const sEmail As String = "C:\ExportedEmail.msg"

Sub SaveFirstUnreadEmail()
    Dim oOlAp As Object, oOlns As Object, oOlInb As Object
    Dim oOlItm As Object, oOlAtch As Object

    '~~> Get Outlook instance
    Set oOlAp = GetObject(, "Outlook.application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

    '~~> Check if there are any actual unread emails
    If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "NO Unread Email In Inbox"
        Exit Sub
    End If

    '~~> Save the 1st unread email
    For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
        oOlItm.SaveAs sEmail, 3
        Exit For
    Next
End Sub

PART - 4

Moving on to your next request

1. Mark the email in Outlook as "read"

See this code example. This will mark the email as read.

Const olFolderInbox As Integer = 6

Sub MarkAsUnread()
    Dim oOlAp As Object, oOlns As Object, oOlInb As Object
    Dim oOlItm As Object, oOlAtch As Object

    '~~> Get Outlook instance
    Set oOlAp = GetObject(, "Outlook.application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

    '~~> Check if there are any actual unread emails
    If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "NO Unread Email In Inbox"
        Exit Sub
    End If

    '~~> Mark 1st unread email as read
    For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
        oOlItm.UnRead = False
        DoEvents
        oOlItm.Save
        Exit For
    Next
 End Sub

PART - 5

Moving on to your next request

1. Open the excel attachment in excel

once you have downloaded the file/attachment as shown above then use that path in the below code to open the file.

Sub OpenExcelFile()
    Dim wb As Workbook

    '~~> FilePath is the file that we earlier downloaded
    Set wb = Workbooks.Open(FilePath)
End Sub

I converted this post into several blog posts (with more explanation) which can be accessed via points 15,16 and 17 in vba-excel

Why do you find it strange that the image is included as an attachment? Without it, there is no way for the recipient to view the image, as the email itself is text only. I imagine the only reason it doesn't show as an attachment in outlook is because outlook is smart enough to know that it's not a "real attachment" per se, but just the signature. Even then, I remember recieving signature images as attachments in older versions of outlook.

That said, it should be possible to use a linked image in your signature. This link explains how to do it.

Creating an Outlook signature with an image or picture located on the Internet requires changing Outlook’s behavior on how to deal with linked images and also adding the image in a specific way. Once the image is inserted, you can use the Signature Editor to turn the image into a hyperlink to a website.

Set Outlook to link to pictures instead of embedding them

The first step is to change Outlook’s behavior on how to deal with linked pictures. By default Outlook will embed them with the message, but this would mean that your picture would be a reflection of when you created the message instead of when the the recipient is reading (or even re-reading) your message.

Depending on how often you change your picture, this might be a non-issue but it would also mean that the picture gets added each time and thus making each message quite a bit larger. In several cases this could also result in your picture being added as an attachment and/or show the message with a paperclip icon. These are all things that you should want to avoid; if not for you, then definitely for the recipient’s sake.

In Outlook 2003 and previous you can change this behavior via; Tools-> Options…-> tab Mail Format-> button Internet Format-> option: When an HTML message contains pictures located on the Internet , send a copy of the pictures instead of the reference to their location.

In Outlook 2007 and 2010, this can behavior can only be changed via the Registry.

Key: HKEY_CURRENT_USER\Software\Microsoft\Office\\Outlook\Options\Mail Value name; Send Pictures With Document Value: 0

For more info about this registry key see; Where did "HTML options" go in Outlook 2007/2010?

Insert image as link When inserting the image into your signature, you must again make sure that the image is linked. If you do not do this, then the current image will be downloaded and saved within your signature and wouldn’t update when you change it on-line.

Outlook 2003 Right click in the Signature Editor (do not use Advanced Edit) and choose; Insert Image… Type the Internet address (URL) to the image in full when prompted for the Picture Source. Click OK

Outlook 2007 and Outlook 2010 Click on the Picture icon in the Signature Editor As the file name, type the Internet address (URL) to the image in full. Instead of clicking on Insert, click on the little down arrow on the Insert button and choose; Link to File

Create a hyperlink

Once the image has been added, you can make it clickable and point it to a website via the the Hyperlink function.

Outlook 2003

Right click on the inserted image and choose; Edit Hyperlink Outlook 2007 and Outlook 2010 Select the image and click on the Hyperlink icon