create a email... from an excel vba app... 2

[Paco75]

Hi,

I want to create a email with an attachment from an Excel application... I guess I will have to use Outlook's functionality to do so! If someone can give me tips or advices on this...

thanks

 

[VBAjedi]

If I understand your question correctly, the following code should work.

First write code to set the values of the ESubject, SendTo, Ebody, and NewFileName (a complete filepath) variables, then use this code:

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
          .Subject = ESubject
          .To = SendTo
          .Body = Ebody
          .Attachments.Add (NewFileName)
          .Send
End With

Set App = Nothing
Set Itm = Nothing

Hope this helps!

VBAjedi

 

[aishaaa]

if u r trying to send a sheet or more u can try this

Worksheets(Array("sheet2", "sheet5&quot).Copy
ActiveWorkbook.SendMail "email@hotmail.com", "test"

 

[SYchai]

I am a novice in programming.I have a project that reading some data from an instrument and keep in a Excel spreadsheet.

The condition is,if the value is greater than and below a certain range, then it will send an email out.

I would be greatly appreciated if anyone help me to write this program.

Thanks
SY

 

[Paco75]

Thanks VBAJedi,

This works #1

 

[Paco75]

Hi there again,

Is there a way to keep the new email opened to allow the user to write the subject/body?

thanks

 

[VBAjedi]

To let the user edit the email, change the code as follows: replace ".send" with ".display", and delete the code that sets App and Itm to nothing. The last part of your code should look something like this:

.Display

End With

Just be aware that with this approach the user has to hit "send" themselves. So it isn't totally automated.

VBAjedi

 

[Paco75]

Yeah, finally the user want to write a personal text... just the attachment will be automated.

Thanks a lot!

 

[thevillageinn]

thanks VBAjedi, this post has been very helpful...

I still have a question though.

In outlook, when using exchange, there is the option to define who the message is "From", not "on behalf of" though. I am unable to figure out where / how I can set this variable. I tried ".SenderName" but that's a read only property.

Is anyone familiar with this variable or where to find it?

many thanks in advance-
-Dan

 

[thevillageinn]

So I was able to get more info in the Outlook IDE, and found that the ".SentOnBehalfOfName" property is the correct one to choose.