Sending Email in Excel using Macro
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
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
ASKED:
November 22, 2010 6:34 AM
UPDATED:
November 22, 2010 4:23 PM
RELATED QUESTIONS
- I want to send a DB2 file via email from AS400 to MS outlook using MMAIL/SENDMIME command.
- Create meeting invitation to all email message recipients
- Access 2007 form creation
- If i use the send again option on a system undeliverable email will that send the email to everyone i originally sent it to or just the recip...?
- Verify name and address entered in Excel in Ms Access table
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
Is it possible without password?
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 !
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…
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 !
Got it! Thanks so much Rechil…