Count Data Then Send To Correct List

Hi,


I have a spreedsheet with a list of areas and items which are in that area.
I need to be able to count the number items in the area and send the count to the area Distribution List until the list ends.


Thank you for time


Nurofen

The data doesn't pair off, London is not London Send Count, how do you match them?

Hi Xld,

Sorry that was just so i could show what I wanted to match.

London Send Count would just be London and then the email address.


Nurofen

Hi,

I've been able to change a cell value and send email to London List, but i'm not sure if it right, if it is right how do i make the same happen for other cells and send to other areas? I'm stuck

Any chance someone can help with the countng number before sending to please.


Thank you all for your time


Nurofen

Hi,


I'm using offset to go do the rows until it hits a cell with something in it how do i show that count of rows before it hits a cell with a value, count entered in cell D8.

Thank you for your time

Nurofen

Got your PM,

What are you actually trying to count in this?

Is your intent to actually place the value in D8?

What exactly do you want the email part to do? It seems you want to send London their total (count?), and each other their respective totals....

but then you also want to send all 4 areas total(count?)

Would be happy to help out, just clarify what it is really want to happen here.

Hi XLGibbs,

Thanks

I just want to count the amount of item for the area and send a email to the correct Distribution list. London has 6 item, copy 6 to the email with a bit of text then send to London list.
the item amount will change so my intent is put the count where ever the count finishes.

Sending all four area counts is just a backup just in case I need to send the information to one person or list

I hope this clear

Thank you for taking time to help

Nurofen

Well, it is clear, and I can likely find a solutoin, but emailing from excel isn't my forte and I would be searching the board for answers same as you. I will post what I can get together tomorrow sometime. Calling it a day for now.

Hi XLGibbs,

No Problem, what ever you can do i'm sure will be helpful.
I'll will be searching too.



Thanks again


Nurofen

Hi,

I found code at Ron's Excel Tips about emails which works on an E address located in A1 of every sheet I have attached a example of how it would work for me but my only problem would be the increase areas would mean more and more Tabs.
Can this be done for match data and send to correct Distribution list?

Please don't think i'm all over the place and not sticking to my original question, i'm just trying to find code and ideas to maybe put together my request to meet my original question.

Thank you for any help


Nurofen

Hi Nurofen,
What does this mean?

Quote:

---

Can this be done for match data and send to correct Distribution list?

---

Hi Lucas,

I want to be able to count the items in London and then
match london to the Distribution list and send the email with the count and same for the others



Thank you for you time



Nurofen

The key here is finding or create code that will simply send the email. From there, doing the count, and or passing the email addresses from a list through that sendEmail mod is much easier.

I am buried with a sudden server issue for work and won't be able to help out today as much as I had hoped.

Hi XLGibbs,

Quote:

---

The key here is finding or create code that will simply send the email. From there, doing the count, and or passing the email addresses from a list through that sendEmail mod is much easier.

---

Sorry But my understanding of what you said is find/create a code that will send the email after the count is complete to the correct list.

Quote:

---

I am buried with a sudden server issue for work and won't be able to help out today as much as I had hoped.

---

No problem XLGibbs hope all gets sorted.

Thank you for taking time to help

I'll carring on searching if I can find anything or someone has a code thats does what you said..


Thank again

Nurofen

What I mean is, it is easier to compartmentalize the tasks.

Primary task: Get code that sends an email directly (not just the dialog box)

Secondary task: modify the above to accept variables passed to it for send To, subject and body.

Third piece: build code that gathers the information to send through the email sub in Secondary task.

Parts 2 and 3 are really simple for me to help with. Part 1 I don't have any experience with, but if you have Outlook, there is tons of code here that can be modified.

XLGibbs,

Quote:

---

sends an email directly (not just the dialog box)

---

Yes I have Outlook just set it up today. Can you just expalin a bit more about the above and I will start searching.

Thanks

Nurofen

Just do a search for "send email outlook excel"

Once you have a procedure that can send an email (send it to yourself for testing) on the click of a button, we can work off that.

http://vbaexpress.com/forum/showthread.php?t=15913 which I have started in the Excel Help

I need help with this sorry i can't be more clear.

Primary task: Get code that sends an email directly (not just the dialog box)


Thank you for your time


Nurofen

I didn't see that post XLGibbs


I've just posted the question in the Outlook Help section

http://vbaexpress.com/forum/showthread.php?t=15925

But i will search for that now


Thanks you really for taking time to help mate :friends:

Nurofen

XLGibbs


I've got that if thats right I hope it is, whats next mate



Nurofen

How do you want the mail to be sent ? Is it by using your personal provider at home or is it through an exchange server ?

I'm using cdo (for short info messages because length is limited) to send automatic requests for more info based on rows that are partially filled in in excel (extra info for payments to be made that I need).

Regarding your set-up. It would be easier if every row in column A would have the department filled in (London, Whatever, ...). You just have to count the occurences of the department then.

Must the mail be sent to two adresses ? Seperate mail for each one or one mail for the two ?

Hi Charlize,

I want the mail to be sent via Outlook, It's from work that the emails will be sent so I
guess exchange server.

I'm not sure my company would allow the use of cdo.(but i will confirm that)

Regarding setup: I have attached a file of my understanding to what you have said.
When you say "count the occurences of the department" will that count how many times London
appears or the amount of items that appear in London? I would like it to count the number of items
item with in each department.

yes I would have to send the mail to two address may be more, it would be just the one mail per
department.

Thank you for your time

Nurofen

A possible idea for mailing sheets. Now the copying of the counting needs to be done to each sheet before mailing it.

Thanks Charlize


Thats looks great hope I can work it into place with other parts.

Have to try find counting methods.

Any ideas of how to go about it or what i should be searching for cause i'm lost.

I've tried offset but can't get it to count, tried select case and that just lost me.

Thank you for taking time to help

Nurofen

Quote:

---

Originally Posted by Nurofen

Thanks Charlize


Thats looks great hope I can work it into place with other parts.

Have to try find counting methods.

Any ideas of how to go about it or what i should be searching for cause i'm lost.

I've tried offset but can't get it to count, tried select case and that just lost me.

Thank you for taking time to help

Nurofen

---

The problem with your list is that you have blanks in a row, it would be simplier when every row has the department name. From there we could make a collection of unique items and for each item in the collection we count the no of items and copy that result to the corresponding sheet (sheet has same name as the item in the collection).

And every department sheet has the mailadresses in A1 and A2

The solution is tested on Excel 2003 (when you are using an older version, i think you have to specifiy a path for saving the temporary file).

Hi,

Charlize has come up with a great way to send the emails, could some guide in the right direction for counting the data and coping the count to the sheets.


Thanking you all at VBAX for your time in helping me


Nurofen

Hi Charlize,

Quote:

---

From there we could make a collection of unique items and for each item in the collection we count the no of items and copy that result to the corresponding sheet (sheet has same name as the item in the collection).

---

:think:

I'm really sorry but i dont' understand what you mean.

I can have one sheet with all the names of the areas in Column A, but the data I have to search for the amount of items is download with blank rows and the item numbers & amounts will change on a daily basis the only thing that stays the same is the areas.


Thank you taking time to help

Nurofen

This coding will add the department for each empty cell in column A for the range C3:C x

Code:

---

Sub add_department_info()
    Dim vDepartment As String
    Dim cell As Range
    For Each cell In Worksheets("Info").Range("C3:C" & Worksheets("Info").Range("C" & Rows.Count).End(xlUp).Row)
        If cell.Offset(, -2).Value <> vbNullString Then
            vDepartment = cell.Offset(, -2).Value
        Else
            cell.Offset(, -2).Value = vDepartment
        End If
    Next cell
End Sub

---

Threads merged at Nurofen's request

Thanks Lucas :thumb

Nurofen

I used this before and with your code Charlize we can get the count, it's just getting the counts to the sheets before sending.

Code:

---

Private Sub cmdCreateTable_Click()
    Dim strHead As String
    Dim strSheetName As String
    Dim strListAddress As String
    Sheets(1).Range("A1:A163").Select
    strHead = Selection.Cells(1, 1)
    strSheetName = "'" & ActiveSheet.Name & "'!"
    strListAddress = Selection.Address(ReferenceStyle:=xlR1C1)
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    strSheetName & strListAddress).CreatePivotTable TableDestination:="", TableName:="CountOf"
    ActiveSheet.PivotTables("CountOf").AddFields RowFields:=strHead
    With ActiveSheet.PivotTables("CountOf").PivotFields(strHead)
        .Orientation = xlDataField
        .Caption = "Count of" & strHead
        .Function = xlCount
        ' End With
        ActiveWorkbook.ShowPivotTableFieldList = False
        Application.CommandBars("PivotTable").Visible = False
    End With
End Sub

---

Thanking you for your support and time

Nurofen

Hi,

Is there any way to have a count feature applied to Charlize Code. so it counts the the department its duplicating.

Code:

---

Sub add_department_info()
    Dim vDepartment As String
    Dim cell As Range
    For Each cell In Worksheets("Info").Range("C3:C" & Worksheets("Info").Range("C" & Rows.Count).End(xlUp).Row)
        If cell.Offset(, -2).Value <> vbNullString Then
            vDepartment = cell.Offset(, -2).Value
        Else
            cell.Offset(, -2).Value = vDepartment
        End If
    Next cell
End Sub

---

I found this: any ideas

Code:

---

Dim Counter as Integer
Dim TheLowestAmnt as Currency
' when an item is found in your loop
Counter = Counter + 1
WhatWasFound = Range("A1").Offset(Counter,0)
TheLowestAmnt = Application.WorksheetFunction.Min(Range("A1:A4")) or whatever the range will be where you write each found occurrence.

---

Thanking you all for your help and time at VBAX


Nurofen

Try this ?

Thank you very much Charlize,
I couldn't use the email part cause it saves the workbook to the c:/ drive and my company does not allow that
so i used Ron.

Just one more thing why do numbers appear on the Croydon Sheet
in Column I ?:think:

Once again thank you for all your help Charlize :friends:

Reagrds

Nurofen:bow:

Thank you to others for time spent on helping :beerchug:

I would just like to say a big thank you to all the people that have helped me on my projects Lucas my friend for his guidence and lots of help on Menu maker Thread Malcolm thank you for your time and making it easy for a novice like me.
XLGibbs for the guidence and help on my Month building code issue Thread
Thank you to Xld for help wiith optionbutton to set commandbutton focus Thread
I would just like to say a big THANK YOU to Charlize who had the great ideas to make my project work Count Data Then Send To Correct List Thread.


Thank you all here that take your time to help with out you there would be no us....thats the truth :bow: :friends: :thumb

Nurofen glad to be a member.

Nurofen,
You're very welcome for my part. It is always good to hear that what we do here is appreciated. My guess is that it won't be long before you are helping others here now that you have become part of the community.....