link to e-mail address

magdalene

New Member
Joined
Sep 13, 2006
Messages
5
how can i put a command button on a spreadsheet and when i click on the button it will automatically sent the sheet to an e-mail address
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Maybe you can adapt this


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 = "Enter in email address"
        .CC = ""
        .BCC = ""
        .Subject = "Enter in 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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,052
Messages
5,545,735
Members
410,703
Latest member
yaronjoseph
Top