Problem with creating an email from Excel 2013

[LGMan]

Hi, a fairly simple procedure for creating an email has been working for me any my co workers for some time on our Win7, office 2010 machines. However one of my co workers has upgraded to Win8 and Office 2013, and the procedure doesn't pull in any of the ranges that are uses for the .To .Subject or .Body fields within the email. In Office 2010, the email gets displayed with all the relevant fields pulled from Sheet3 (my helper sheet), however in 2013, the email opens in display mode, but all the fields are blank. Any help as I've no experience of Win8 what so ever.

here's my code

Sub SendAMail()
 
Dim oOApp As Object
Dim oMail As Object
Dim RngTo as Range
Dim RngSub as Range
Dim RngBdy as Range
 
Set oOApp = CreateObject("Outlook.Application")
Set oMail = oOApp.CreateItem(0)
Set RngTo = Sheet3.Range("F36")
Set RngSub = Sheet3.Range("D36")
Set RngBdy = Sheet3.Range("E36")

 On Error Resume Next
' Change the mail address and subject in the macro before you run it.
 With oMail
     .To = Rngto
     .CC = ""
     .BCC = ""
     .Subject = RngSub
     .Body = RngBdy
     .Display 
 End With
 On Error GoTo 0
 
 Set oMail = Nothing
 Set oOApp = Nothing
 
End Sub

 

[SkipVought]

hi,

Dim RngTo as String
Dim RngSub as String
Dim RngBdy as String
 
Set oOApp = CreateObject("Outlook.Application")
Set oMail = oOApp.CreateItem(0)
RngTo = Sheet3.Range("F36").Value
RngSub = Sheet3.Range("D36").Value
RngBdy = Sheet3.Range("E36").Value

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LGMan]

Thanks Skip, my co worker has finished for the day, so I can't test it out, however on Excel 2010, it results in a compile errorbject required
Would you expect that?

 

[SkipVought]

on what statement?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LGMan]

Sorry my error, read your reply too quick and didn't notice that I had to remove the Set from Set Rngto etc.

 

[LGMan]

Something that I've wanting to do for a while connected with the email is rather than have it as .Display, I want to change it to .Send
However, I run into problems if outlook isn't already open. Is there a way to open Outlook if it's not already open.

 

[SkipVought]

how about

oOApp.Visible = True

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LGMan]

Sorry no luck

 

[Andrzejek]

You may want to give a try this approach, with CDO

More information here

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Ditto to that!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}