Another outlook email body conundrum

[superjonboy]

Hello and thanks for taking the time to view my post.

I've been programming and coding for quite a while but mainly in web based situations (html, css, php) and so on. Recently, a small project i'm working on for my friends business has given me a real headache. It involves an order slip being sent by email.

Scenario

Outlook receives new email
An Outlook Rule runs a script
The subject of the NEW email is put into a String
The body of the NEW email is put into a String
Excel opens and the subject & body strings are pasted into seperate cells
From Excel a label is then automatically printed and Excel closes without saving any changes to the template file (I don't need to backup the order slips to Excel as they are backed up elsewhere)

Problem

Now I have everything working but I just have a small, albeit fundamental, error in that: The code I've made Get's the Selected Email in outlook rather than the new email.
To clarify I need to get the Subject & Body of the latest/last/newest email to arrive into my outlook inbox.

Code

Ok here's what I have so far


Public Sub Excel_Send(Mail As Outlook.MailItem)
Dim myOlSel As Outlook.Selection
Dim strText As String

Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet

If Application.ActiveExplorer.Selection.Count = 1 Then
Set myOlSel = Application.ActiveExplorer.Selection
strText = myOlSel.item(1).Body
strTitle = myOlSel.item(1).Subject

Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open("C:\xxxxxxxxxxxxx\lblPrint.xlsx")
Set xlWs = xlWb.Sheets(1)

xlWs.Activate
xlApp.Application.Visible = True

With xlWs
.Cells(1, "A") = strTitle
.Cells(2, "A") = Trim(strText)
.Cells.WrapText = True
End With
End If
End Sub


Conclusion

So what I have at the minute is working, but it is getting the body of whichever email is selected as apposed to the latest email received.

As I say this has got me beaten now. Hopefully it will just be a simple fix to have the latest email selected but I just can't work it out. I would massively appreciate any help.

Kind Regards,
John

[JP2112]

If your rule fires whenever an email is received, then your rule passes the MailItem object into it and you have direct access to that email. Just use Mail.Body, Mail.Subject, etc.

[superjonboy]

Thanks for your reply. It's greatly appreciated.

I'm still stuck though, I don't quite get it.

I kept the same sub and then put this inside it

Dim subject As Object

Set subject = Mail.Subject


But now I keep getting Compile Error: Type mismatch

[JP2112]

The Subject Property returns a String, why are you declaring an Object.


Dim subject As String
Set subject = Mail.Subject

[superjonboy]

Ah I think it was erroring because I used Set.

I've taken that out and it works now. I can't believe how much I was overcomplicating that!

Thanks so much for your help!

[JP2112]

Oops, I missed that too!