email macro

T

terilad

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 [email protected] filled in.

Can anyone help me with this.

Many thanks

Mark
 
J

Jeff

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 = "[email protected]"
.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
 
T

terilad

Many thanks Jeff

Mark

Jeff said:
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 = "[email protected]"
.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
Click to expand...
 
Top