Email button on worksheet

RLB

New Member
Joined
Oct 10, 2005
Messages
13
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Maybe you can adapt my code - Note - you will have to input the addressee & title

Code:
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

New Member
Joined
Oct 10, 2005
Messages
13
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,
 

Forum statistics

Threads
1,136,588
Messages
5,676,675
Members
419,643
Latest member
ishanalikhan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top