I have a macro in which I will trigger it to run automatically, under windows scheduler. Eg:
"D:DataMacro.xls".
I am expecting to pass parameter in such way, which I did for my other exe application.
"D:DataMacro.xls" parameter_1.
I want the macro to read the parameter_1 in such way.
I used Scheduled Task to set up my spreadsheet to open daily. I have the code with assistance to pull out the due date items and place them into an email.
I have come across XLSTART/AUTOEXEC/ACTIVATE...ETC...
But cannot figure out the code that will automatically "enabling macro" once Scheduled Task opens the spreadsheet?
Then once the macro runs, the email with the due dates, how can this auto send without user interaction?
(currently I would have to hit send)
I am trying to make the process totally automated to open the spreadsheet at a certain time, send the email with due dates and close the spreadsheet.
Following code in ThisWorkbook--
Private Sub Workbook_Open()
Check_Date_Send_Mail
End Sub
Code in Module1--
Option Explicit
Sub Check_Date_Send_Mail()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnDate As Range, rnValue As Range
Dim stAddress As String, stMsg As String
Dim stRecipient As String, stSubject As String
Dim stPost As String
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")
With wsSheet
Set rnDate = .Range("d2:t23")
End With
I though I could do this with a nested IF statement but it is too cunfusing for me. What I am trying to accomplish is this:
Experiment
Is Steward
EU ID
Location
Data Quality
GE
Entry Order
[Code] ........
I want to have a screen pop-up asking me what my limit < would be for column "ESTCNT" so if I put in 25 or any other number that it would highlight all the rows that are less than 25, then look at the row above and below and if it matches the same number (that is in the cell "Range" of the highlighted column) in column "Range" then copy that row to a new sheet. Meaning all tha rows that match the "Range" would be in the same new sheet.
The rows might be different lengths and that there will not always be a number in cell "ESTCNT". Column headers will always be the same but might not be in the same column each time. And if it is not to hard once it is completed to find column "SPPLOT" in the new sheet created and asking what I want to autofil the column with.
add parameter to this macro and at the end the process to index the highest result at the end, is that possible?
Here is the macro;
Code:
Sub Macro()
Range(Selection, Selection.End(xlDown)).Select
Calculate
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range
[Code]..
to run 5000 times
Last time i got macro from this forum how to import files automatically. I am importing the data from the specific folder.In code itself we are hardcoding that file names.But in that folder i have lot of files is there any option to pass any parameter value.That means each time it will asks for filename(Prompt).Once you give file name it will automatically load the data.
[Code] .....
I've written a function to delete the charts on a worksheet: ....
View 9 Replies View RelatedI am running a macro to loop through a list of codes which takes the unique code, goes to a different spreadsheet, finds related data based on that code and if it meets certain criteria, and retursn the same code if it works. Pretty much a filtering process, but I have it returning the code because I do not want to do the calculations in the Macro itself.
Here is the code:
VB:
On Error Goto Label1
Do
Windows("Byron_MonthlyPDPWellList.xlsx").Activate
Range("A" & I).Select
[Code] .....
"I" begins at 2 and counts up until it reaches Count, which is the number of total rows in the column.
However, when I step through the code, it continues to loop through the code where "I > Count".
I have tried "Until I > Count", "Until I = Count", "While I <= Count". And none of them seem to work. It gets stuck in an endless loop cycle.
We use proprietary software to connect to an oracle database. The proprietary software has its own login form. I connect to 4 or 5 different databases randomly throughout the day. I wrote a script that I thought would alleviate my login woes but it doesn't work. The login, password, and database info get sent to the form but they all end up on the login line. Instead of the tab character being sent, I get a Beep generated for each line of code that is supposed to send the tab key code. My code is below and is stored in a *.vbs file.
setwshShell =wScript.CreateObject("WScript.Shell")
wshShell.AppActivate "Title Of My Login Form"
wScript.Sleep 100
wshShell.SendKeys "My Login Name"
wScript.Sleep 500
wshShell.SendKeys "{TAB}"
wScript.Sleep 500
wshShell.SendKeys "My Password"
wScript.Sleep 500
wshShell.SendKeys "{TAB}"
wScript.Sleep 500
wshShell.SendKeys "Name of My Database"
wScript.Sleep 500
wshShell.SendKeys "{TAB}"
wScript.Sleep 500
wshShell.SendKeys "~"
I have found the below mentioned code from one of the threads.
Sub EmailDoc()
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")
Dim olMail As MailItem
Set olMail = olApp.CreateItem(olMailItem)
Application.ScreenUpdating = False
With olMail
.To = Range("A1").value
.SentOnBehalfOfName = """xyz"" "
.Subject = "Resources"
'.Attachments.Add "D: estxyz.xls"
.send
End With
Set olMail = Nothing
Set olApp = Nothing
End Sub
The above code works fine but i want to send emails to all the names in my column A in the To field and in the CC field from column D (It shoulds send if there are any values for the CC field)
Is it possible to send an email if an error occurs with all the information regarding that error? I've created a workbook with a lot of macros and a lot of information. It's used by several people and sometimes those people have several other excel workbooks open and running at the same time. I've done my best to fix all the bugs but should one come up I'd like to be able to have the workbook send me an email alerting me of the error and the details surrounding the error such as what the error was, what macro was running, ranges selected, all that useful info. Is this even possible? or is there some sort of work around to at least bring up a prompt or page for the user to fill out then send by email after the error occurred?
View 2 Replies View RelatedI want to send via FTP to a linux server the following.
1- All files that are named checked.XXX.YY.mtc to path /var/docs
2- All files where the name begins with checked.conf to path /etc
PD: I have the IP address, user and password of the server.
I finally found 'Sendkeys' and by having it press {ENTER} I was able to get my data to do what I wanted.
However now I cannot print my page. I tried everything.
Sub Macro2()
'
'
Sheets("List").Select
ActiveSheet.Cells(5 , 2).Select
Selection.Copy
[Code] ......
I can copy/paste other things just fine after the SendKeys but CANNOT PRINT!
I am trying to find a way of sending an email when a something comes out of date.
View 9 Replies View RelatedIm looking to create a reporting mechanism for a shared worksheet that im putting together.
I am looking to create some that will automatically create an email (either through outlook, or using a userform) and automatically populate with my email address in the 'To' section and automatically populate the subject with 'Error/Info Change'. The user can then populate the main email body with whatever they want to notify me of.
Im then hoping that the code can be run through a macro which has been added to the quick access toolbar.
I want to automate (with the click of a macro button, or hot key sequence) is to:
· take a pre-set print area (which is always the same – 1 page wide x 1 page tall)
· send to the printer
· select the PDF printer from the list (not the default printer for the computer)
· Automatically change the save PDF file name to “(Cell A1) – Weekly Update – (Today’s Date).pdf”
· Change location to save file on Desktop
Feels like a pretty lofty goal for a macro to do all of that – please let me know if that is even possible to set up.
I need the VBA code to automate my repeated task. I have the range of 100 rows and has the date field in a column and i need a macro to extract the rows for three previous days starting from today.
View 2 Replies View RelatedI have attached a sample workbook. The workbook already has a macro that can send an email after working out if the date is overdue. I have put a button on the screen, but I would like to remove the button and automate it, I got the initial macro from,( whoever initially put it together) but I have since made some changes to it regarding how the message displays etc. i, 15 or i, 16 in the macro means, There isn't anything in column i and I can't work out the relevance of it.
View 1 Replies View RelatedI got this VBA code from a colleague which he uses when sending multiple emails from Outlook. This code sends email based from the last cell. What I would like to do is send emails to all the recipients listed in column C. Column A will be the sender and column B will be email subject. Attached is the sample file.
[Code] .....
Send email.xlsm‎
I would like a macro that recognises the cell colour in column F if it is yellow i would like a standard email to be created & sent to the corresponding customer email address in column D. is this even possible??
macro visual basic and trying to do a simple report wherein i can send an email auto matically.
View 2 Replies View RelatedUserform that people use. I need validating the users input into the form.
1. Need to validate that A1 only has 3 letters (Alpha)
2. Need to validate that A2 is not left blank
3. Need to validate A3 has only 10 digits
My state is that when the user hits the submit button in the user form the macro checks for these validations above and if everything is ok emails the sheet. However if the user havent inputed the data correctly a msg box will pop to let them know what is wrong. If thats the case i do not want the sheet emailed. below is the email code i'm using
HTML Code:Â
Sub SendMail()
' Copy the sheet(1)
ThisWorkbook.Sheets(1).Copy
' Send Email
.SendMail Recipients:=Array("email.address@email.com"), Subject:="Test" & Format(Date, "dd/mmm/yy")
.Close SaveChanges:=False
End With
End Sub
How to modify the below vba code? It sends an email with an attachment right away after pressing the macro button. I would like the user to see the email in outlook before it gets send... and then press 'send' in Outlook .....
Sub Mail_ActiveSheet()
'Working in Excel 2000-2013
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
Dim FileExtStr As String
Dim FileFormatNum As Long
[Code] .....
creating a macro to send email from excel through Outlook basis of different conditions and with embedded text and subject.
There 2 workbooks attached with this post. The workbook named "Data" contains the data and the workbook named "Emails" contains the Dealer Names (in Column A), the Email ids of the contact person (Column B, C and D), the subject (in Column E) and mail content (in Column F).
I need a Macro which first split the data basis of "Dealer Name" Column F into multiple sheets from "Data" sheet. Now, each sheet will contain only the Individual dealer's data. The data whose ageing is 2 or more than 2 days (in Column H) is highlighted in yellow.
Now, I need to send a separate email to dealers with their data details from range A to F (including headers) as an embedded text with subject and mail content which is present in "Emails" workbook.
Now the twist is, if any dealer having the Aging value in Column H is 2 or more than 2 days (highlighted in yellow color) than the email should be sent in CC to Column D email ids but if there is no Aging more than 2 days than the email should be sent in CC to Column C email ids.
"To" email ids (in Column B), subject (in Column E) and mail body (in Column F) will remain same in both conditions. Column A is containing the Dealer Names through which macro will identify the email ids.
I am trying to make an inventory list with the expiry date of various chemicals. There is a user form which I have not coded yet. What I hope to do is have the macro send an email regarding which chemical is expiring within 90 days to a group of people.
View 3 Replies View RelatedI have a template invoice in excel. What I want is a macro code that when it is run the open template invoice should be sent to a specific email address !!
View 5 Replies View RelatedThe following line of code is part of the great work that this forum did in creating a macro to send an automated email through Lotus Notes.
I have a small tweak that requires the Body of the message to exist on sheet other than the one where the macro is launched.
The originial code had:
Body1 = Replace(Join(Application.Transpose(Range([by9], [by18].End(3))), "@") & "@@Thank you,", "@", vbCrLf)
I tried:
Body1 = Replace(Join(Application.Transpose(Sheets("LookUp Sheet").Range([by9], [by18].End(3))), "@") & "@@Thank you,", "@", vbCrLf)
But the body of the email is not being copied.
Am I putting the calling the Sheets reference incorrectly?
The routine works perfectly if the body data comes from the sheet where the macro is launched?
I want to send the value of selected item in texbox , the value format is change by combobox value ..and I want the value is fill on the next column of sheet ( see attached file please)
View 2 Replies View RelatedI am using 2007. This may be something simple but I can't seem to find a solution to this, I have an excel macro enabled workbook which produces a daily report the final task is to save and send an email but I need the file to save as an xls rather than xlsx as some of the recipients are unable to open the file here is the code used for the tasks in the
workbook:Sub Step1_Refresh()
'
' Step1_Refresh Macro
'
'
ActiveWorkbook.RefreshAll
End Sub
Sub step2_save_close()
[Code] ........
I am using 2007, I have an excel macro enabled workbook which produces a daily report the final task is to save and send an email but I need the file to save as an xls rather than xlsx as some of the recipients are unable to open the file.
Here is the code used for the tasks in the:
workbook:Sub Step1_Refresh()
'
' Step1_Refresh Macro
'
'
[Code]....
I currently have a spreadsheet setup on a Macro to send & distribute a message from an 2007 Excel file to multiple users through Microsoft Outlook 2007. It is currently setup to send as an attachment. I need to find a way to have this file be sent as a PDF file. I know you can save Excel as PDF's so there must be a way to send them & attach them to as a PDF.
View 3 Replies View Related