Email button on worksheet

[RLB]

Hi,

I'm looking to create a button on a worksheet that creates an email with pre-populated address and subject fields, so that the user can then just fill in the message body and click send.

I'm not very good with code, so any help would be appreciated... the simpler the better!

Thanks in advance...
Rebecca

 

[DominicB]

Good morning RLB

You don't say which e-mail client you're working with. v. v. important...

Ron de Bruin provides all the code you could ever need for working with Outlook (/ Express) over here :

http://www.rondebruin.nl/sendmail.htm

Or if you'd rather use his SebdMail add-in try here :

http://www.rondebruin.nl/mail/add-in.htm

HTH

DominicB

 

[MarkAndrews]

Maybe you can adapt my code - Note - you will have to input the addressee & title

Sub Mail()

ActiveWorkbook.Save
'You must add a reference to the Microsoft outlook Library
     Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim Mail As Variant

Mail = MsgBox("Click OK to automatically email this workbook", vbInformation + vbOKCancel)
If Mail = vbCancel Then
Exit Sub
End If


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    strbody = Application.InputBox("Would you like to enter a message as part of the email?", "Email message", Type:=2)
 
    With OutMail
        .To = "Addressee Here"
        .CC = "Addressee Here "
        .BCC = "Addressee Here "
        .Subject = "Email Title" & Date
        .Body = strbody
        .Attachments.Add ActiveWorkbook.FullName
        On Error Resume Next
        .Send   'or use .Display
        If Err > 0 Then MsgBox "You clicked 'No' - Therefore the email was not sent"
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
    MsgBox "Providing you clicked Yes, this email will appear in your Sent Box in Outlook"
    ActiveWorkbook.Save
    ActiveWorkbook.Close
 
End Sub

 

[RLB]

thanks for the replies

this is getting quite technical for me!

right then... the email client is outlook and this is the code that works so far:

Sub ContactUs()
Dim outlook As Object
Dim namespace As Object
Dim mailitem As Object
Set outlook = CreateObject("Outlook.Application")
Set mailitem = outlook.createitem(0)
With mailitem
.to = "a@b.com; b@b.com"
.Subject = "Subject"
.noaging = True
.display
End With
End Sub

the only problem being that this is being done through citrix and once the send button has been sent it is just sitting in the outbox on the citrix box.

i've been told that there is a way around this, and the code used here in FoxPro to get around this is:

oOutLookObject = Createobject("Outlook.Application")
oEmailItem = oOutLookObject.CreateItem(0)
With oEmailItem
.Subject = "Subject"
.Importance = 1
.Body = "body"
.Display
Endwith
Do While Type('oemailitem.importance')<>"U"
Enddo
oOutLookObject.Application.Quit
oOutLookObject=Null

is there a way to do this in excel/vb? i'm stuck at the 'do while type.....' bit...

sorry for the long post!

thanks,

 

[RLB]

any ideas anyone??!!??!

 

[MarkAndrews]

Non, sorry - Citrix isn't a strength point