Hi,

I have a worksheet and I want to add to a cell that has text in it, email
administrator, what I am looking to do is when this cell is selected a
message box will appear to ask do you want to email the administrator, yes or
no.

When yes is clicked I need the outlook application on the PC to start up and
new message to appear with email address filled in.

Can anyone help me with this.

Many thanks

Mark

Here is one way, Place this code behind the sheet with the cell you want to
use as a trigger. (Right-Click sheet name , View code) HTH.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Test to see if desired cell is select
If Target.Address = "$A$1" Then
' query user to Email workbook
Call QueryEmailAdmin
End If
End Sub

Private Sub SendMailWithAttachment(ByVal aFile As String)
' Check to see if the file exists
' you could add code to prompt the user
' to save the file first then send it
If Len(Dir(aFile)) = 0 Then
MsgBox aFile & " is not a valid directory", vbCritical, "Error"
Exit Sub
End If

'Declare Variables
Dim Outlook As Object
Dim Mail As Object

'assign Variables
Set Outlook = CreateObject("Outlook.Application")
Outlook.Session.Logon
Set Mail = Outlook.CreateItem(0)

'Build Email
With Mail
.To = "
.cc = ""
.Bcc = ""
.Subject = "Hello World"
.Body = "This is a macro test"
.Attachments.Add aFile
.Display
'.Send
End With

Set Mail = Nothing
Set Outlook = Nothing
End Sub

Private Sub QueryEmailAdmin()
Dim Response As Integer
Dim Msg As String

Msg = "Do you want to email the administrator?"

Response = MsgBox(Msg, vbYesNo + vbQuestion, Application.Name)

' Get response from user
If Response < vbNo Then
'affimitive response
'Pass the workbooks full path to the
'SendMailWithAttachment procedure to attach email
Call SendMailWithAttachment(ThisWorkbook.FullName)
Else
' Negitive response
MsgBox "Email Canceled by user", vbInformation
End If
End Sub



"terilad" wrote:

Hi,

I have a worksheet and I want to add to a cell that has text in it, email
administrator, what I am looking to do is when this cell is selected a
message box will appear to ask do you want to email the administrator, yes or
no.

When yes is clicked I need the outlook application on the PC to start up and
new message to appear with email address filled in.

Can anyone help me with this.

Many thanks

Mark

Many thanks Jeff

Mark

"Jeff" wrote:

Here is one way, Place this code behind the sheet with the cell you want to
use as a trigger. (Right-Click sheet name , View code) HTH.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Test to see if desired cell is select
If Target.Address = "$A$1" Then
' query user to Email workbook
Call QueryEmailAdmin
End If
End Sub

Private Sub SendMailWithAttachment(ByVal aFile As String)
' Check to see if the file exists
' you could add code to prompt the user
' to save the file first then send it
If Len(Dir(aFile)) = 0 Then
MsgBox aFile & " is not a valid directory", vbCritical, "Error"
Exit Sub
End If

'Declare Variables
Dim Outlook As Object
Dim Mail As Object

'assign Variables
Set Outlook = CreateObject("Outlook.Application")
Outlook.Session.Logon
Set Mail = Outlook.CreateItem(0)

'Build Email
With Mail
.To = "
.cc = ""
.Bcc = ""
.Subject = "Hello World"
.Body = "This is a macro test"
.Attachments.Add aFile
.Display
'.Send
End With

Set Mail = Nothing
Set Outlook = Nothing
End Sub

Private Sub QueryEmailAdmin()
Dim Response As Integer
Dim Msg As String

Msg = "Do you want to email the administrator?"

Response = MsgBox(Msg, vbYesNo + vbQuestion, Application.Name)

' Get response from user
If Response < vbNo Then
'affimitive response
'Pass the workbooks full path to the
'SendMailWithAttachment procedure to attach email
Call SendMailWithAttachment(ThisWorkbook.FullName)
Else
' Negitive response
MsgBox "Email Canceled by user", vbInformation
End If
End Sub



"terilad" wrote:

Hi,

I have a worksheet and I want to add to a cell that has text in it, email
administrator, what I am looking to do is when this cell is selected a
message box will appear to ask do you want to email the administrator, yes or
no.

When yes is clicked I need the outlook application on the PC to start up and
new message to appear with email address filled in.

Can anyone help me with this.

Many thanks

Mark