Sending Email in Excel using Macro

135 pts.
Tags:
Access 2000
excel
Excel macros
Outlook
VBA Access 2000
Hi,

I want to create a button in excel wherein once you click that button a message will pop-up and ask "Are you sure you want to send notification to your supervisor?" Yes/No. If yes is selected a New Outlook Message will appear where recipient's email address is already given and the subject is already available as weel as the body email. If the No buton is selected the message box will close.

Is this possible?

Thanks i advance.



Software/Hardware used:
MS Excel / MS Outlook

Answer Wiki

Thanks. We'll let you know when a new response is added.

U can assign this code to a command button or extricate the portion(s) you want and place there . Modify body text and password as and other info as per ur requirement: this is a sample code ….

Sub Email()

Dim testPassword As String
testPassword = InputBox(prompt:=”Please enter the password:”, _
If testPassword <> “Password” Then
MsgBox prompt:=”Click OK to return to Report.”, _
Buttons:=16
Else
Dim Resp As Integer
Resp = MsgBox(prompt:=”Click Yes to review email, No to immediately send, or Cancel.”, _
Buttons:=3 + 32)
End If
Select Case Resp
‘Here Yes was clicked, user wants to review email first
Case Is = 6
Dim myOutlook As Object
Dim myMailItem As Object
Set otlApp = CreateObject(“Outlook.Application”)
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & “” & ActiveWorkbook.Name
With otlNewMail
.To = “emailid@….com; name@…..com”
.CC = “” (email id of cc person)
.Subject = “Email from me”
.Body = “Attached is today’s Report.” & Chr(13) & “Regards,” & Chr(13) & “Greg” & Chr(13) & Chr(13)
.Attachments.Add fName
.Display
End With
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing

‘If no is clicked
Case Is = 7
Dim myOutlok As Object
Dim myMailItm As Object
Set otlApp = CreateObject(“Outlook.Application”)
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & “” & ActiveWorkbook.Name
With otlNewMail
.To = “email@….com;”
.CC = “same as above”
.Subject = “Email from me”
.Body = “Attached is today’s Report.” & Chr(13) & “Regards,” & Chr(13) & “Greg” & Chr(13) & Chr(13)
.Attachments.Add fName
.Send
End With
otlApp.Quit
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
‘If Cancel is clicked
Case Is = 2
MsgBox prompt:=”Click OK to return to Report.”, _
Title:=”EMAIL CANCELLED”, _
Buttons:=64
End Select
End Sub

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Eron09
    Is it possible without password?
    135 pointsBadges:
    report
  • Subhendu Sen
    This is a sample code, and u have to eliminate the password related code. I provided for ur better security, that no one can use this process and send mail without informing U !
    27,510 pointsBadges:
    report
  • Eron09
    Oh I see that's great! But how can I set the password? I tried it and it's asking for a password but I dont know how to set it up...sorry...
    135 pointsBadges:
    report
  • Subhendu Sen
    I told u to eliminate the portion of password code ! however when it asks for password simply type: Password I mentioned in the code where the line belongs : If testPassword <> "Password" Then.... and here u may change ur own password by replacing the word "Password" Thanks !
    27,510 pointsBadges:
    report
  • Eron09
    Got it! Thanks so much Rechil...
    135 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following