I am using the following code and it works great the only problem is that when I have more then one email address in the same cell it will not send the email. Even if I seperate it with a semicolon. It work fine if I have just one email address in the email field. How can I get it to send the same info to different email addresses.
My vb code works well for sending worksheets to multiple recipients (about 400 rows) IF I have only one email address in a cell, however, I have some rows that have more than one email address (eg. firstname.lastname@example.org, email@example.com). I've spent hours researching a clean way to do this and I think I need to be able to extract each of them and assign them to a variable to use in an array???
I'm using Lotus Notes 6.5 and Excel 2003.
'Here is where I get the email address to put in the mailAddress variable Workbooks("GSA Mileage Report.xls").Activate mailAddress = "" On Error Resume Next mailAddress = Application.WorksheetFunction. _ VLookup(EmailAddrSheet.Cells(RowNum, 1).Value, _ Worksheets("MailInfo").Range("A1:B" & _ Worksheets("MailInfo").Rows.Count), 2, False) On Error GoTo 0
I have Excel WB / Sheet with filled columns A (serial No.), B (name), C (e-mail adress), D (problem), E (solved / unsolved) and now I want that Excel automaticly sends e-mail notification to specific e-mail adress (column C) when its / his problem is solved (column E).
I have a code that i got from here which will send the Workbooks named in col A to email addresses in col B, as below..
but i need it to send the named Worksheets from the currently active Workbook.
The name of the Worksheet will be contained in col A.
Sub SendWkbs() Application.ScreenUpdating = False Dim wks As Worksheet Dim iRowA As Integer, iRowB As Integer Set wks = ActiveSheet iRowA = 2 On Error GoTo EH Do Until IsEmpty(wks.Cells(iRowA, 1)) iRowB = 2
I'm a primary school class representative and I want to create an excel contact list of the parents email addresses so that I can click a button, it will open the default email (in my case MS Outlook) and then populate the "To" email address field with the email addresses (separated by a comma).
Can anyone provide me the VB code that I can paste into my worksheet VBA that will look at a range of cells (say E2:E30) that contain email addresses, and put them in the "To" field of a blank email?
I have put a button on the page and it's called "EmailButton" but I don't know what VB to put with it. I'm using MSExcel 2007 (at home) and 2003 (at work)
I am using the following code to try and send a workbook via attachment to multiple email recipients however it will not work. If I only include one recipient then the code runs fine however it crashes when there is more than one.
1) Prompt user to select a file (or multiple files)
2) Copy the files that meet certain criteria to a folder
My attempt (fail): VB:
Sub FILES2SFTP() Dim FileNames As Variant Dim I As Integer Dim fso As Variant Dim Data As String ChDrive "G:" ChDir "G:TEST" Data = InputBox("Enter the date", "Enter the date", Format(Application.WorksheetFunction.WorkDay(Date, -1), "yyyymmdd")) Set fso = [code].... I get error 424 object not found in this line:
If fso.getfilename(FileNames(I).Name) = ("Name1" & Data & ".xls" Or "Name2" & Data & ".xls") Then
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")
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
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.
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria: First Letter of the Unique ID in column "E" - O, M, or L Program Type in Column "F" - U or R 1. O-U = U 2. O-R = RU 3. M-U = U2 4. M-R = R2U 5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB: Option Explicit Sub test() Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long Set dic = CreateObject("Scripting.Dictionary")
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4 89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g. AAA blue BBB orange AAA round CCC smelly AAA elongated
Worksheet 2 I want to show: A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g. AAA blue round elongated BBB orange CCC smelly
I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z Well: 1 0 0 Back Bar: 0 1 0 Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
I would like to create a formula in Col G that does the following:If any of the values in columns B to F equals OK, then OKIf any of the values in columns B to F equals KO, then KO I've been tinkering with multiple IFs and quite rightly got bogged down as too many criteria to take into account...
So, another question with regards to this sales sheet that i am setting up. I want to be able to take the information below and for each of the people listed (i.e. by their initials) i would like to...
1.) Take their associated % (Columns C, E, G) 2.) Multiply the % by the Revenue (Column H) 3.) Sum up the total for each of the names (Locatedin Columns B, D, F) 4.) Only sum up the ones that occur in Month 1 of the Year 2012
I have tried multiple ways with a Sumproduct but none of them have worked. I would love to be able to do this with a single formula without having to add another column, for instance, to track what MonthNum it is. I tried using some kind of offset, but i could never get it to work.. For instance to look through the range, find the initial go over 1 column to the right and multiply that % by the revenue if the Month is 1..
Date (A)Rec1Name (B)Rec1Pct (C)Rec2Name (D)Rec2Pct (E)Rec3Name (F)Rec3Pct (G)Revenue (H)1/1/12AD25.00%RU25.00%BF25.00%1000.001/1/12AD25.00%RU25.00%PNF25.00%2000.001/1/12BF50.00%00.00%00.00%1200.002/1/12BF50.00%00.00%00.00%1000.002/1/12BF50.00%00.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1500.004/1/12AD16.67%RU16.67%BF16.67%1500.004/1/12BF50.00%00.00%00.00%1500.00
I would like a macro that select all cells based on multiple criteria. It's a big sheet, but I've made it smaller in the following example.
In row 1, there are 2 headers, in B1 (electricity) and C1 (gas) In column A, there are 3 houses, in A2, A6 and A10. The blocks of cells in between (e.g. B2:B5) have 2 headers, 'period' in B2, and 'account number' in B4. B3 and B5 have a list of options in them.
I want to select cells that meet certain criteria. Either electricity or gas, and either period or account number. So, if in cell D1 I choose electricity and in D2 period, I would like to select B3, B7 and B11. If I choose gas and account number, I wish to select C5, C9 and C13.
I have a range of expiry dates in column F of a sheet with the description of the item expired in column B. Is there a nice little Macro which would ideally send an e-mail out to certain adressees to warn of the expiry or just have a report which is printable showing items which have expired. I would ideally like the report to be generated automatically.
get the code for sending multiple emails as per the spreadsheet list. Assume the spreadsheet has 100 line items and each columns specifies the name of the person, value, recipient email address ("To" and "CC") and sender name.
And the Body of text is :
Hi "Name of the person" Please find the value of "Value" to be paid for the moth of xxx and kindly let me know for further clarification.
send the sheet to an email address based on the email address in a cell.
The cell populates from a dropdown list a user selects.
The email will be sent to myself, and one authoriser. If a second authoriser is required then the first authoriser will send this on. I'm trying to make this simple, so when a user clicks a 'send' button on the form this happens automatically.