Help in VBA generating outlook mail

[vigneshjan24]

Hi,

I have some help in sending email using macro. Since i dont know anything about vba, just googled and using this macro to send mails by selecting any range.

Sub Send_Selection()
  Dim Sendrng As Range
  On Error GoTo StopMacro
  With Application
  .ScreenUpdating = False
  .EnableEvents = False
  End With
  Set Sendrng = Selection
  With Sendrng

  ActiveWorkbook.EnvelopeVisible = True
  With .Parent.MailEnvelope
  .Introduction = "Hi," & vbNewLine & vbNewLine & _
  "" & vbNewLine & _
  "Please refer the below"
  With .Item
  .To = "xyz@gmail.com"
  .CC = "abc@gmail.com"
  .BCC = ""
  .Subject = "Test"
  .Send
  End With

  End With
  End With

StopMacro:
  With Application
  .ScreenUpdating = True
  .EnableEvents = True
  End With
  ActiveWorkbook.EnvelopeVisible = False

End Sub

I need help for below queries within the mail generating,

1. Default outlook signature should be added at the end of the mail.
2. Need to get rid of the automatic horizontal line between the bod of the mail and the range of cells i selected.
3. I donot want to send automatically. It should be in draft to edit. I tried
.Display
.Save
instead of .Send but the draft mail is not opening unless i close the excel. I dont want to close the excel but need to edit the mail in outlook.


Thanks in advance,
Vigneshwaran P
vignesh[dot]jan24[at]gmail.com

 

[Kmahraz]

@vigneshjan24
If you do a quick search in the threads you should find an answer.
in the mean time I will recommend, that you take a look into this link
http://www.rondebruin.nl/win/s1/outlook/mail.htm
they have a list of sample files, videos and tutorial to Mail from Excel with Outlook
Here's an example that will meet your need
Example
The following subroutine sends the last saved version of the active workbook in an e-mail message. Change the mail address and subject in the macro before you run it.

Sub Mail_workbook_Outlook_1()
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.to = "ron@debruin.nl"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
'.Send 
 .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub