Solved: Count Data Then Send To Correct List

[Charlize]

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 ?

[Nurofen]

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

[Charlize]

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

[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

[Charlize]

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).

[Nurofen]

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

[Nurofen]

Hi Charlize,

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).

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

[Charlize]

This coding will add the department for each empty cell in column A for the range C3:C x[VBA]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[/VBA]

[lucas]

Threads merged at Nurofen's request

[Nurofen]

Thanks Lucas

Nurofen

[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.
[vba]
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[/vba]

Thanking you for your support and time

Nurofen

[Nurofen]

Hi,

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


[VBA]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 [/VBA]


I found this: any ideas

[VBA]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.[/VBA]


Thanking you all for your help and time at VBAX


Nurofen

[Charlize]

Try this ?

[Nurofen]

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 ?

Once again thank you for all your help Charlize

Reagrds

Nurofen

Thank you to others for time spent on helping

[Nurofen]

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

Nurofen glad to be a member.

[lucas]

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.....