I use a Macro that when run, will generate an email with the excel sheet attached. See attached example.
My two questions are:
- Is it possible for the body of the email that is generated to have spaces within the text? So it shows more like the below:
"Good morning,
Please find today's forecast for Friday, May 07, 2010 attached.
FRC Treasury Team"
- Also, the colors in the spreadsheet change automatically when the macro attaches the sheet. Why is this? Is it possible to do something so that this does not happen?
I have a VBA macro for automatically create a word report using data in Access and Excel. The macro is hosted within a Excel document but are using both Word-, Excel and Access objects/dataTtemplates for the creation.
The errors appears what it seems randomly. The macro iterates the same code a few times even in Office 365 but then it crashes. The most common error messages are:
Error 1:
Run-time error '-2147417851 (80010105)':
Automation error : The server threw an exception.
Error 2:
Run-time error '462':
The remote server machine does not exist or is unavailable
Error 3:
This action cannot be completed because the application () is busy. Choose Switch To to activate and correct the problem.
(no options are working, this hang the application)
If I debug usually the error originates from the code where I ether close the workbook template I'm using:
excelWorkbook.Close False
... or when I paste a chart from Excel to the Word report:
wordDocument.ActiveWindow.Selection.PasteSpecial
Even more strange are that sometimes if I continue to run the macro its all working again. This ONLY happens when the client office installation is O365. Office 2013 works just fine. I thought O365 and Office 2013 was fairly equal?
Why this error is coming up on a macro. Now to what I'm trying to accomplish is I have 1 sheet called GDL and I want to pull certain data from that sheet into another sheet called data sheet. I have formulas set in the data sheet to pull the information I want. However, I want to create a macro to pull the data for those formula or I guess another way of looking at it would be a auto fill function, except with a macro button. Please see the following error I receive when trying to run the macro.
Run-time error '1004': Autofill method of range class failed. The following is the macro.
VB: Sub ResetDataSheet() ' ' ResetDataSheet Macro ' Macro recorded 11/13/2012 by asdf ' [Code] ....
I have an excel sheet which contains Supplier certs issue date and date of expiry of a particular cert. I would like to send an email to user when the date of expiry is met. I have very little knowledge or no knowledge of coding in VB. I want to send the email 30 days before the cert expire. i have attached the file.
I send these kind of mails to intimate people about upcoming meeting, the format I prepare in Excel 2013 and paste it in Outlook 2013 (as a table, not as an image)
Now one of the columns I put as 'Days Remaining' which basically tells the users how many days are there to the review, the image below will show how it looks:
Now the numbers of days remaining will be correct the day I send the mail, but when somebody opens the mail at a later date, it would not sort of show the true number.
Is there a way to auto-update this number inside the Outlook mail, as it works in an Excel Sheet?
I have been getting this '5' error a lot lately and I dont know why. Many of my macros have had this error. Run-time error '5': Invalid procedure call or argument. The offending (Highlited in Red)
I have a macro to send emails to a group people that based on various criteria, it will attach a number of files to a recipient's particular email. This is a small portion of the code that does the attachment adding:
Code: If Range("B" & a) = "Y" Then If citChev "" Then .Attachments.Add citChev End If If Range("C" & a) = "Y" Then If citMits "" Then .Attachments.Add citMits End If If Range("D" & a) = "Y" Then If citToyo "" Then .Attachments.Add citToyo End If If Range("E" & a) = "Y" Then
[code].....
Most people get several of the files attached to their email and all works fine. But, there are certain conditions when all the IF() stmts fail where a recipient will not get any files attached. I do not want to send the email if this is the case, but it currently is sending it.
Is there a way after all the IF() stmts have processed to check to see if this current email has any attachments assigned to it? (IF .Attachments "" Then...) does not work.
Im 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.
My Macro sends a PDF to a "To:" recipient and a "CC:" recipient. Both email addresses are referenced from cells. Here are the first four lines of the code:
I'm trying to create an excel macro that can send emails with an attached file per row of my entry in excel I imagine it would be like the screenshot below. I've tried the code in the link below from a macro tutorial website but wasn't able to make it work due to error DataObject is unkown I'm using excel 2013 and lotus notes 8.5 as my email.
I am currently attempting to use an excel macro to allow me to send a list of files to one person. I have managed to create a dynamic list where one sheet of the workbook has the list of files and checkboxes, and as certain ones are selected they appear in a list on the 'front sheet' of my workbook, which has the button to send the email.
My front sheet is set up in a way that in cell B3 I have the desired email address, B4 contains the subject and from B5 to B30 I have the list of selected attachments (although the length of the list obviously varies depending on the amount of files selected, when all are selected the list extends to B30).
I have the below vba code that allows me to create an email with the address and subject and attaches the file address from cell B5, but I would like to attach all the files in the list and am struggling to change the code to do so.
Code: Sub CreateMail()
Dim objOutlook As Object Dim objMail As Object Dim rngTo As Range Dim rngSubject As Range Dim rngAttach As Range
Set objOutlook = CreateObject("Outlook.Application")
[Code] ........
I can add more by creating new ranges (rngAttach1, rngAttach2, rngAttach3 etc) and setting these to their respective locations but if, for example, only two files are selected, the list only covers cells B5 and B6, so when the macro looks up the file directory in B7 (rngAttach3) it returns an error because obviously there is nothing for it to find.
I have attempted to compile a loop and change the code to the version shown below but am having no luck.
Code: Sub CreateMail()
Dim objOutlook As Object Dim objMail As Object Dim rngTo As Range Dim rngSubject As Variant Dim rngAttach As String Dim iLoop As Long
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
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.
I 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 !!
I am using Excel 2010. I have been given a task at work that can save my team a lot of time if I can solve the problem. Every month, we have a spreadsheet with about 5000 rows that we have to email. In each row, there is a range that we have to email to a specific email. For example, I would have to copy and paste Range A2-R2 in the body of the email, and then email it to whatever email is in cell S2. I would then continue this for the next 5000 lines, making it a possibility that i will be sending 5000 emails manually.
I have been trying to come up with a solution through VBA that would automatically send these emails. My goal is to automatically send the Range A2-R2 to outlook email, then cell S2 into the "To" email address box, and then automatically send it. So far, i have successfully been able to send one row, but cannot figure out how to loop it for the remainder of the rows.
A couple other key points are that I have column headings as well (Range A1-R1). If possible, I want to be able to include the column headings in the email body as well. Example - first email would be range A1-R2. second email would be range A1-R1 and A3-R3, and so forth. The body of the email would also contain a standard script, such as "Please review the information below."
The goal here is to save everyone from having to send 5000+ manual emails. This would be a big boost for my team.
I 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()
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.
We know that professional emails have a fixed format. I have a list of people with their names and emails and there are some people for which emails are missing. I am pasting my sample data below. But first let me explain the meaning of columns.
Column A: Means full name of the person for which we have email id Column B & C: just split of Available full name into first name and last name Column D: Email id of the person with full name in column A Column E: For the names in this column, we need to get email ids Column H: Sample result
Now basis on the Full Name(Column A) and Email Id(Column D) can we get the email id for person in Column E.
Sheet4 A B C D E F G H
1 Available Full Name Available FirstName Available LastName Email for available full name Actual Full name Actual firstname Actual lastname Sample result
I can get it to work with 1 email and 1 condition but not 2.
Sub auto_open() ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange" End Sub
Sub DidCellsChange() Dim KeyCells As String KeyCells = "A1:A99" If Not Application.Intersect(ActiveCell, Range(KeyCells)) _ Is Nothing Then KeyCellsChanged End Sub ................................
I've been using a macro successfully for the last couple of years, but this morning when I went to use it, it decided to fail. I have a workbook which contains various spreadsheets. The macro that has failed performs the following tasks:
1. It copies a list of email addresss from an external workbook to a sheet in the current workbook (still works) 2. It copies the referral sheet I want to send to a new file, and saves it with an appropriate filename to an appropriate folder (still works) 3. The macro then creates an email with a standard subject line, attaches the new worksheet and emails it to each of the addresses as above (broken)
The error message is from Microsoft Visual Basic. "Run-time error '1004': Mail system failure. Check your mail installation."
I'm guessing there is a setting somewhere in Excel that has changed as part of an update.
I've been through a number of the options in the developer menu to remove any obvious restrictions (& reopened excel afterwards), but so far it hasn't resolved the problem.
For what it's worth, here is the macro code.
With Application .EnableEvents = False .ScreenUpdating = False End With Run "PullInSheet1" Dim oldbook As String
[Code] .......
In case you're wondering about the pullinsheet code, I'll add it below - but I probably grabbed it from this forum a couple of years ago (like some of the above) & just made some changes.
Code: Sub PullInSheet1() Dim AreaAddress As String '''''' Sheet11.UsedRange.Clear Dim ClRange As String ClRange = "= 'L:ADMINEMPLOY SERVICES" _
[Code] ........
There are a couple of things I've wanted to do to improve the macro, but I couldn't justify the need to spend time working it out (since writing spreadsheets isn't really my job). Since it's broken at the moment, I can...
1. I'd like to create a subject line that reflects the name of the person being referred. For some reason though, anything other than text in the cell reference caused an error for me. eg, I tried using concatenate to create my subject line, but it didn't work.
2. I'd like the copied sheet to contain all of the formatting of the original sheet. Presumably there is a paste option that will do this and I just picked the wrong one.
The mailsystem we use is Groupwise 8. I couldn't find any settings in that program that have been changed, or that I could change.
I should change the extension from xls to xlsx in the code since I'm using Excel 2010 (but changing it doesn't fix the problem).
I have a macro that is set to clear a range of cells. If I make a change to the worksheet (such as adding a column) that moves those cells in the worksheet, the worksheet formulas update, but the macro cell references do not. How can I make my macro update in accordance with changes in the worksheet?
I need to send an email when value of timer in column F exceeds 72 hrs. E-mail address of recipient is in column C and 2 fixed addresses are to be put in C.C.
basic outline would be
name(A) work(B) E-Mail(C) Work Started (D) Work Finished(E) Time Elapsed for Work from Start (F)
Can this emails be sent to repeat also after 24 hrs if the timer in F is not stopped (i.e till no entry is put into Column E)?
look at the script im running in the attached spreadsheet. It auto generates an email to the email address in c so long as column H has a yes in it. However there are things i would like to change. I would like it to say: contact xxx to discuss your outstanding resource center loan titled: {then put the title of the load in here - column E} which was due back on {then put due back date here column G}.
I wrote this code years ago to Auto Send Emails from Outlook...
Sub Emailfile()
Dim OutlookApp As Object Const olMailItem = 0 Set OutlookApp = CreateObject("Outlook.Application") Dim EmailContent As String With OutlookApp.CreateItem(olMailItem) EmailContent = Range("A1").Value .To = "Test" .Subject = "LSG Data File" .Body = EmailContent .Attachments.Add ("P:MI TeamICCE LEADS EXTRACTSLSGLSG Data File")
End With
End Sub
I can't figure out how to get it working for Lotus notes... So 2 things I guess really... Firstly is it possible? Second: If it is, how can I get it to send say 16 emails each with a specific attachment but the same commentary?
