Free Microsoft Excel 2013 Quick Reference

Command button control and send email automatically

Hello All
Tks to lend your hands for me.

I would like to use a command button to display msg(as notice) for the record requested.And then if click ok in the msgbox will lead to sending a file named the recordno to a nominated person.
Attach here the file and the plan.

Let me know if it is possible to use VBA to make it.Tks in advance.


Post your answer or comment

comments powered by Disqus
Hi all,

If someone can help me that would be really appreciated. I have several workbooks on a sharepoint, and need a couple of things;

1. There are numerous spreadsheets on the sharepoint, column 1 has a date within it, is there a way to have an automatic process that would check all spreadsheets daily and send an email to alert there are jobs pending. Can this be done without opening the spreadsheet itself?

2. I need to be able to email to various people data from indididual rows. Is there a way to select a row click a button (macro) and send the data, leaving the "to" blank? I.e. Column 10 has notes in it, but would like to include data from all rows so it would look something like:

DATE: "insert date from spreadsheet"
JOB NUMBER:
COMPANY:
NOTES:

etc etc

Any help much appreciated.

Thanks DJ

ctu1...@gmail.com Mar 23, 8:23 am show options

Newsgroups: microsoft.public.tw.excel
From: ctu1...@gmail.com - Find messages by this author
Date: 23 Mar 2005 08:23:40 -0800
Local: Wed, Mar 23 2005 8:23 am
Subject: Send email automaticly in excel
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

Hi there,
I add some finance data from http://finance.yahoo.com to a excel file.
The finance data in my excel file will change every minutes.
I am monitoring Nasdaq index. I expect it will go to 1900 soon.
Because of my job, I can not access to internet all the time.
I am wondering, can my excel monitor the nasdaq index? When nasdaq
goes to 1900, can my excel send me a notification email to my Hotmail
account? And the hotmail will send a short message to my mobile phone.

Best regards,
Charles

Hello new guy to forum and VBA for that matter... but need some help...

Ok, So I am trying to creat a looping code that will grab emails from 1 list and populate them in my code that I use to send auto send emails in lotus notes, and then pick the attachment location from another list in excel... So essentially I am looking to send 1 specific file (pdf's) to a specific person... than loop to pick the next person in the list and the next location... so the end result would be that I have sent 10 emails with attachments to 10 different recipients with a unique file attached to each. Below is my code that I sue to attach and send emails from excel through Lotus notes...

'Starting to build email
Dim oSess As Object
Dim oDB As Object
Dim oDoc As Object
Dim oItem As Object
Dim direct As Object
Dim Var As Variant
Dim flag As Boolean

Set oSess = CreateObject("Notes.NotesSession")
Set oDB = oSess.GETDATABASE("", "")
Call oDB.OPENMAIL
flag = True
If Not (oDB.IsOpen) Then flag = oDB.Open("", "")

If Not flag Then
MsgBox "Can't open mail file: " & oDB.SERVER & " " & oDB.FILEPATH
GoTo exit_SendAttachment
End If
On Error GoTo err_handler

'Building Message
Set oDoc = oDB.CREATEDOCUMENT
Set oItem = oDoc.CREATERICHTEXTITEM("BODY")
oDoc.Form = "Memo"
oDoc.Subject = "Budget File"
oDoc.sendto = "example@example.com"
oDoc.body = "whatever I would like to type same message for all recipients."
oDoc.postdate = Date
oDoc.SaveMessageOnSend = True

'Attaching DATABASE
Call oItem.EmbedObject(1454, "", "H:example.pdf")
oDoc.visable = True
'Sending Message
oDoc.SEND False
exit_SendAttachment:
On Error Resume Next
Set oSess = Nothing
Set oDB = Nothing
Set oDoc = Nothing
Set oItem = Nothing
'Done
Exit Sub
err_handler:
If Err.Number = 7225 Then
MsgBox "File doesn't exist"
Else
MsgBox Err.Number & " " & Err.Description
End If
On Error GoTo exit_SendAttachment

End Sub

Hello OzGrid!
I am trying to figure out the best way to write code that will do all of the following, and I'm not even sure where to start (I have done some searching in the forums and have some ideas, but not sure how to pull them all together):
1) Keep track of or highlight cells that have been changed on all sheets in the workbook except the "JMF Print" sheet
2) Compile all changes from objective 1 into a log of some sort
3) Send an email to a group of people telling what changes have been made
4) Not require any/very much additional action by user

I know I will probably have to write at least 2 different macros for this; I'd like the "email" macro to be activated by pushing a button. I sortof have in mind that when the user is done making a group of changes, he/she could hit the button and send the email automatically. This file gets modified several times daily (at least...), and I don't want to clog people's inboxes with every single change that get made. I'd like the rest of the code to not require the user to do anything "extra".

Other concerns:
A) The people who will be using this file are extremely unfamiliar with Excel, so this has to be pretty easy and quick.
B) The list of people to be emailed needs to be easily changed.
C) Our email program is GroupWise

I've attached a sample file that will give you an idea of what I am dealing with, although the actual file is way bigger...

Thank you in advance for your help!!!

I was creating a spreadsheet (forms) to collect feedback and I put a
button on called "submit" to get people submit the data...

The code is as follows:

Sub Button1_Click()

MsgBox "Survey Completed. Thanks a lot!"

ActiveWorkbook.Save

Application.Dialogs(xlDialogSendMail).Show

End Sub

In this way I can get the email program open automatically when people
click the button.

But Can I actually modify the code somehow and get my email address
filled and send the email out automatically?

Thanks, P

--
prague
------------------------------------------------------------------------
prague's Profile: http://www.excelforum.com/member.php...o&userid=30172
View this thread: http://www.excelforum.com/showthread...hreadid=513097

I was creating a spreadsheet (forms) to collect feedback and I put a button on called "submit" to get people submit the data...

The code is as follows:

Sub Button1_Click()

MsgBox "Survey Completed. Thanks a lot!"

ActiveWorkbook.Save

Application.Dialogs(xlDialogSendMail).Show

End Sub

In this way I can get the email program open automatically when people click the button.

But Can I actually modify the code somehow and get my email address filled and send the email out automatically?

Thanks, P

Has anyone got or seen a code that can send a automated email via outlook.

i do not want to send the workbook or worksheet but just to be able to send a text only email when command button is pushed?

I would like to send a simple email notification when a button is clicked. How do I write a VB code to fill up some constant string value in the subject line and Send email to the recipient using MS Outlook.

Thanks in advance for your help.

I want to add a command button to save a copy of the worksheet I have in the user's "My Documents" folder; If possible using the name of the client in cell B8 and a time stamp as the file name. BUT I also need to have the worksheet sent to email addresses based on which checkbox is checked. For Example Checkbox 1 is for the parts department, it is checked. When the command button is clicked, the file is saved and a copy sent to the parts department email. Is that possible?

I am trying to create a userform and when an Analyst has completed populating the form with informations, he/she clicks on 'Done and send' button. I want excel to perform the following duties after clicking the 'Done and Send' button:
1) Prompt user to save the file
2) Open the Analyst's email, create an attachment (in this case last xls file saved), and send an email to john.doe@isitpossible.com
I thank you for your help.

All,

Using Excel XP, I have created an Add-In which contains a worksheet. When
the add-in is loaded, the worksheet is populated with data and then a copied
to the client workbook. The worksheet also contains a command button.

Two issues:

1. If I use the Control Box command button, it is not copied to the client
worksheet along with the rest of the form. The click event code appears in
the general section of the form, but the control itself is not copied.

2. If I use the Forms command button, it is copied correctly to the client,
but the "Assign Macro" value still contains a reference to the Add-In (i.e.
"MyAddIn!CommandButtonAction"). If the user saves the client worksheet and
then happens to re-open it when the Add-In is not loaded, clicking the
button causes an immediate run-time error, that I am unable to trap.

I'd like to have the command button refer to a procedure in the copied
client worksheet so I'll have an opportunity to check for the existence of
the Add-In and raise an appropriate error if it is not loaded.

I have no preference as to which type of command button that I use, as long
as it performs correctly.

Any suggestions will be most appreciated.

Thanks,

Mark

Hi

Im wondering if anyone can help me. Ive got some code that creates an attachement and emails it out for me through lotus notes. The only issue I have is i want to copy some cells on an excel worksheet and paste it into the body of the email as a picture, but for the life of me i cant get the picture into the email. Here is the code im currently using which works for attaching and sending the email with lotus notes:
Sub Button1_Click()
Dim noSession As Object, noDatabase As Object, noDocument As Object
Dim obAttachment As Object, EmbedObject As Object
Dim stSubject As Variant, stAttachment As String
Dim vaRecipient As Variant, vaMsg As Variant

Const EMBED_ATTACHMENT As Long = 1454



Recipient01 = Sheets("Distribution List").Range("a1")
Recipient02 = Sheets("Distribution List").Range("a2")


vaRecipient = Array(Recipient01, Recipient02)

vaMsg = "Please Find Attached My Attachment."
stSubject = "My Attachment"
stAttachment = ActiveWorkbook.FullName
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
Set noDocument = noDatabase.CreateDocument
Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
With noDocument
.Form = "Memo"
.SendTo = vaRecipient
.Subject = stSubject
.Body = vaMsg
.SaveMessageOnSend = True
End With
With noDocument
.PostedDate = Now()
.Send 0, vaRecipient
End With

Set EmbedObject = Nothing
Set obAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing
AppActivate "Microsoft Excel"
End Sub


All,

Using Excel XP, I have created an Add-In which contains a worksheet. When
the add-in is loaded, the worksheet is populated with data and then a copied
to the client workbook. The worksheet also contains a command button.

Two issues:

1. If I use the Control Box command button, it is not copied to the client
worksheet along with the rest of the form. The click event code appears in
the general section of the form, but the control itself is not copied.

2. If I use the Forms command button, it is copied correctly to the client,
but the "Assign Macro" value still contains a reference to the Add-In (i.e.
"MyAddIn!CommandButtonAction"). If the user saves the client worksheet and
then happens to re-open it when the Add-In is not loaded, clicking the
button causes an immediate run-time error, that I am unable to trap.

I'd like to have the command button refer to a procedure in the copied
client worksheet so I'll have an opportunity to check for the existence of
the Add-In and raise an appropriate error if it is not loaded.

I have no preference as to which type of command button that I use, as long
as it performs correctly.

Any suggestions will be most appreciated.

Thanks,

Mark

hi all, here is my problem. thx 4 your time

i have an xla where i create a command button programmatically and not physically. In other words, if you look at the form there is no command button but when the form is open...there it is. Until this point everything is ok.
here is the code that i am using......


	VB:
	
) 
With cltadd 
    .Caption = "New Code" 
    DoEvents 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
problem: when i press the command button called cmdaddd nothing happens. the code for the click action doesnt work. However, i noticed, that if i create the command button in the form the on click action works.


	VB:
	
 cmdaddd_Click() 
     '==============================='
     
    MsgBox "pressed" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
any clue for this behaviour?

thx a lot, max.

Hi All

I hope someone can help. I'm using Excel 2002 on and XP machine. I am creating a spreadsheet to track a work plan.
Column A: Name
Column B: Date
Column C: Time
Column D: YES
Column E: No
Column F: New Date
Column G: New Time

What I'm looking to do is input clickable buttons in Columns D: Yes and Column E: No.
When a user clicks on YES, a pop up window should appear with the data in columns A,B,C. The p0p-up should contain CONFIRM and CANCEL buttons.
Asking the user if they want to confirm the date and Time.
If CONFIRM is clicked Excel should then send an email to me and the user to say that the work schedule has been confirmed.
If CANCEL is clicked the pop-up window closes.

Secondly if the user clicks NO, a pop-up window should open with two free text boxes. One for Proposed date and One for Proposed time. And again 2 buttons for Confirm and Cancel.
If Confirm is clicked then Excel should record the data entered in the free text boxes into Column G and Column H and send emails to me and the user with the Proposed date and Time Data.
If CANCEL is clicked the pop-up window closes.

Please can someone help me out with code for this?

Many thanks in advance.

I am trying to set up a 'main menu' spreadsheet in a work book this contains
command buttons ONLY which navigate to other spreadsheets within the workbook
or other workbooks. I set up up the command buttons via the control toolbox
command button option and inserted hyperlinks to the spreadsheets or
workbooks I want to go to. BUT when I exit design mode inthe contol toolbox
the command buttons don't work. What am I doing wrong? This seemed so much
easier that using the command button in the forms toolbar and creating
macros. Also when I use the command button in forms toolbar I am unable to
change the background of the command button? I can change the font but not
the putty background?

How do you get to the Properties menue for a command button? When I click
design mode and then select my command button. When I select the command
button I want properties for I click "Properties" on the toolbar. The only
options it gives me in this properties box is "Sheet 2 Worksheet" no command
button.
It looks like the good old properties box but with no command button
properties. Am I just not naming the command button properly or something?

Thank you,
Josiah

Hi Friends / Geniuses / Observers

Wondering if you can help me to create a macro to send emails.

1. Attached is an excel spreadsheet. In column A, each time the word total is seen, i want an email to be sent to to someone with the total in colum E.

Eg.
A B C D E
UAFEQ1 TOTAL xxxx xxxxxxxx xxx R 15 000
ALPROP TOTAL xxxx xxxxxxxxx xxx -R 20 000

For UAFEQ1 TOTAL I would like a mail to be sent to john@example.com
For ALRPOP TOTAL I want like a mail to be sent to peter@example.com

If the total in column E is positive i want the mail to say:
Please see deposit of R 15 000

If the total in colum E is negative i want the mail to say:
Please see withdrawal of -R 20 000

The currency is Rands (South Africa)

Sometimes, i need to send 20 or more e-mails. And these can repeat themselves so, there may be two totals for UAFEQ1 TOTAL and a mail must be sent each time. See attached as an example.

The macro can stop when Grand Total is reached. No email needs to be sent for the grand total.

Thanks a million for you help!!

Regards
Dean

Does anyone have a macro which will enable me to open Outlook from an Excel form.

I have a multi sheet Excel form which is sent out to various people and, once they have completed it, I need a macro button which will open Outlook, attached the completed form and send it back to me.

Is this possible?

Thanks

Help!

I have been working on a VBA script in Excel that is supposed to save then attach an Excel file to an Outlook email. The distribution email list is on the "distribution" spreadsheet. After sending the email, the content on the "work order" is cleared so that the "template" file is ready for the next running of the script.

I am having challenges with Line 3 and Line 24 of the following VBA macro depending on the version 2007/2010 that I am using:

Sub SubmitAndLogWorkOrder()
Dim c As Range
Dim olApp As Object
Set olApp = New Outlook.Application
Dim Msg As Oobject
Set Msg = olApp.CreateItem(Outlook.olMailItem)

'Switch to Work Order worksheet and save so this is the first page looked at when the document is opened by someone
Sheets("Work Order").Select
With ThisWorkbook
.SaveAs "S:Maintenance Work OrdersMaintenance Work Order - " & Format(Date, "yyyy-mm-dd") & ".xls", FileFormat:=56
End With
'Switch back to the Distribution sheet for reading/sending emails
Sheets("Distribution").Select
'Send message to recipient email list
With Msg
For Each c In Range("B6", Range("B6").End(xlUp))
.Recipients.Add c.Value
Next c
.Subject = "West Campus Maintenance Work Order"
.Body = "Please note that the attached maintenance work order has been generated at " & Now() & " and submitted to _
West Campus maintenance personnel. This work should be completed within 24 hours. Please notify Jasmine Allen (978-8310, _
jasmine.allen@wichita.edu) If there will be expected delays In completion Or If there are any questions relating To the attached _
work order. Thank you For ensuring timely completion of this task. -- West Campus Staff"
.Attachments.Add ("S:Maintenance Work OrdersMaintenance Work Order - " & Format(Date, "yyyy-mm-dd") & ".xls")
.Send
End With
'Switch back to Work Order worksheet so that the content sent is removed for the next run of the macro

Sheets("Work Order").Select

Range("A3:14").Select
Selection.ClearContents

End Sub

When Excel 2007 is run, Line 3 breaks the code:
Dim olApp As Outlook.Application
Compile error: Can't find project or library

However, when Excel 2010 is run, Line 24 breaks the code:
.Recipients.Add c.Value
Run-time error '287': Application-defined or object-defined error

Ideally, I would like this code to be able to successfully run on either 2007 or 2010 Office platforms. Any help is greatly appreciated!

Augustine

Hi guys,

Lets say I store a list of email addresses in column A. Then I have a set text in another cell.

I need a code that will take that text, paste it into a new email (Outlook 2007) and send it to the first email address in the list. Then repeat for the second, third, fourth and so on until it has sent an email to each of the addresses.

Any ideas?

How do I Enable/Disable Command Buttons from a Sheet?

I am using a Command Button in a Sheet to copy and paste the Data from one Sheet to another with the help of macro..

As I am not well-versed with VBA dont know all the syntaxes of VBA.
I need help for the command button..

First and Foremost, I double click a Command Button form the Control tool-box and paste it on the Sheet, I dont know how to get the name of this command button , I mean where do i get it?

Based on a condition like a value in a cell I want it to be Enabled and Disabled?

Any ideas...please I am not able to follow even after googling a lot as I dont know what's the name of the command button control I have used.

If the value entered in a particluar cell is more than the 1000 difference between two cells then the command button should be disbaled...

The Application part:
The command button is used to transfer the data in a cell lets say $I$4 to another sheet Cell J2,J3,J4 so on so forth..by incrementing the ROW number.

Now The balance gets depleted with every new Debit Entry and we need to disallow the user from entering such an amount which will reduce the balance more than The Minimum Account Balance of a bank...

Hope I am able to explain well..

Warm Regards
e4excel.

Hi all,

I have a workspread that it is able to send email from a macro. I like to improve it so that it can do the following things:
1) Send an email automaticallywhen it is 30 days before the due target date (column G).
2) Include in the email the row that have the due date item and if possible in the email's subject too.

Is that anyway I can do it?

Attached is my test file on this.

Appreciate any help on this.

Hello everyone.

I have a worksheet that opens a simple userform when the file is opened with 2 command buttons (bOwner and bUser). It checks to see who has opened the file (Application.UserName), looks up the persons ID in a list to determine if they are an owner or a user of the file. If the person is not an owner, then the Owner button is disabled (bOwner.Enabled = False). Each button has vba attached to hide or show certain sheets in the file. I have disabled the "X" This part works fine.

My question is, there are some people that will try to click the "Owner" button even though it has been disabled. Can I have a message box appear that tells them they have to choose the other button (even though this one is disabled)?

TIA

Candee


No luck finding an answer? You could always try Google.