vba code needs amending

[classmaz]

Hello I have inherited a database that exports 116 reports/files from a vba code. However I would like to amend the code to also email the 116 reports to the relevant 52 people. The email address are located in the Sales Area table which has 3 fields and 52 rows:

Sales Area
Area code
Email

The code below cycles through this table splitting the main table by area code.

sub Experiment()

'On Error GoTo ErrorCheck

Dim db As Database
Dim qdf As QueryDef
Dim Pcount As Integer
Dim Pprogress As Integer
Dim NidsCount As Integer
Dim BreaksCount As Integer


Set db = CurrentDb

NidsCount = 0
For Each qdf In db.QueryDefs
If qdf.Name Like "*Daily Nids Report" Then
NidsCount = NidsCount + 1
End If
Next

BreaksCount = 0
For Each qdf In db.QueryDefs
If qdf.Name Like "*Daily Breakdown" Then
BreaksCount = BreaksCount + 1
End If
Next

Pcount = BreaksCount + NidsCount

DoCmd.OpenForm "FRMnidsprogress"

Pprogress = Forms![frmnidsprogress].Box4.Width / Pcount

Forms![frmnidsprogress].Text6 = "Running Nids And Breakdowns"
Forms![frmnidsprogress].Box5.Width = 0
Forms![frmnidsprogress].Box5.BackColor = 15898517

For Each qdf In db.QueryDefs
If qdf.Name Like "the Daily breakdown" Then
Else
If qdf.Name Like "*Daily breakdown" Then
DoCmd.TransferSpreadsheet acExport, 8, qdf.Name, "G:\nids test\emails\progressnids\" & qdf.Name & "_" & Format(Date, "ddmmyy") & ".xls", True, ""
Forms![frmnidsprogress].Box5.Width = Forms![frmnidsprogress].Box5.Width + Pprogress
Forms![frmnidsprogress].Box5.BackColor = 15898517
End If
End If
Next

For Each qdf In db.QueryDefs
If qdf.Name Like "the Daily Nids Report" Then
Else
If qdf.Name Like "*Daily Nids Report" Then
DoCmd.TransferSpreadsheet acExport, 8, qdf.Name, "G:\nids test\emails\progressnids\FYI" & qdf.Name & "_" & Format(Date, "ddmmyy") & ".xls", True, ""
Forms![frmnidsprogress].Box5.Width = Forms![frmnidsprogress].Box5.Width + Pprogress
End If
End If
Next

DoCmd.TransferSpreadsheet acExport, 8, "Sales Scotland Unique Apps", "G:\nids test\emails\progressnids\FYI" & "Sales Scotland Daily Nids Report" & "_" & Format(Date, "ddmmyy") & ".xls", True, ""
'Sales Scotland Unique Apps
'Postcode reports
'Dim db As Database
Dim Area_Code_Table As Recordset
'Set db = CurrentDb

Dim LAF_Code As Integer
Dim Sales_Area1 As String
Dim Base_SQL As String
Dim QueryDefName As String
'Dim qdf As QueryDef
Dim RptName As String


Base_SQL = "Select [Daily Postcode Report].[CountOfAppnameref],[Daily Postcode Report].[Postcode],[Daily Postcode Report].[Addr First Line],[Daily Postcode Report].[To User],[Daily Postcode Report].[Application Ref],[Daily Postcode Report].[Application Name],[Daily Postcode Report].[Search Creation Date],[Daily Postcode Report].[Post Applied For],[Daily Postcode Report].[Notification id],[Daily Postcode Report].[LAF Code] from [Daily Postcode Report]where [Daily Postcode Report].[LAF code]="

Set Area_Code_Table = db.OpenRecordset("Postcode Report List")

Do While Not Area_Code_Table.EOF

LAF_Code = Area_Code_Table("Area Code")
Sales_Area1 = Area_Code_Table("sales area")

'QueryDefName = Sales_area1 & "" & " Hardship Case Escalations"
QueryDefName = "Daily Postcode Report Export"
CurrentDb.CreateQueryDef QueryDefName, Base_SQL & "'" & LAF_Code & "'"

'If DCount("*", Sales_area1 & "" & " Hardship Case Escalations") = 0 Then
'CurrentDb.QueryDefs.Delete QueryDefName
'Area_Code_Table.MoveNext
'Else

If DCount("*", "Daily Postcode Report Export") = 0 Then
CurrentDb.QueryDefs.Delete QueryDefName
Area_Code_Table.MoveNext
Else

RptName = Sales_Area1 & "" & " Daily Postcode Report"

Dim XLapp As New Excel.Application
Dim worksheet As Excel.worksheet
Dim ObjXL As Excel.Workbook
Set ObjXL = XLapp.Workbooks.Open("G:\nids test\emails\progressnids\template\Postcode report Template DO NOT DELETE OR MOVE.xls")
ObjXL.Application.Visible = True
ObjXL.Windows(1).Visible = True
ObjXL.Worksheets(2).Activate
Set worksheet = XLapp.Worksheets(2)
With worksheet
.range("A:J").ClearContents
End With
ObjXL.Save
ObjXL.Close
XLapp.Quit

DoCmd.TransferSpreadsheet acExport, 8, "Daily Postcode Report Export", "G:\nids test\emails\progressnids\template\Postcode report Template DO NOT DELETE OR MOVE"

Set ObjXL = XLapp.Workbooks.Open("G:\nids test\emails\progressnids\template\Postcode report Template DO NOT DELETE OR MOVE.xls")
ObjXL.Application.Visible = True
ObjXL.Windows(1).Visible = True
ObjXL.Worksheets(1).Activate
killsometime
ObjXL.SaveAs "G:\nids test\emails\progressnids\" & RptName & "_" & Format(Date, "ddmmyy") & ".xls", True, ""
ObjXL.Close
XLapp.Quit

Dim Skill As String
Skill = "taskkill /F /IM msexcel.exe"
'Shell Skill, vbHide

CurrentDb.QueryDefs.Delete QueryDefName
Area_Code_Table.MoveNext
End If
Loop





Exit Sub

Forms![frmnidsprogress].Box5.BackColor = 7095511
Forms![frmnidsprogress].Text6 = "Nids And Breakdowns Complete"


End Sub

any help appreciated

[OBP]

So is the "Area Code" in the Sales Area Table the same as the LAF Code?

[classmaz]

So is the "Area Code" in the Sales Area Table the same as the LAF Code?

Yes they are the exact same thing

thanks

[OBP]

So, the only place that I see the LAF Code used is in the Base_SQL statement where it is set using this code
LAF_Code = Area_Code_Table("Area Code")
So I would assume that it would be used to iterate through the Sales Area table to find matching codes and then send an email to the email addresses attached to the Area Code?

[classmaz]

Yes that should do it but unfortunately I wouldn’t know how to code it

[OBP]

That is the part that I do, but I obviously need to get it in the right place and using the right data.
What Emailing software do you use?

[classmaz]

The software is Microsoft outlook do you need the version?

[OBP]

I shouldn't do, but you will have to set a VBA Editor Library Reference to it.
I suggest that you take a copy of the database where you can test any code that I supply until you are happy it is doing what you want.

[classmaz]

I shouldn't do, but you will have to set a VBA Editor Library Reference to it.
I suggest that you take a copy of the database where you can test any code that I supply until you are happy it is doing what you want.

Yes that’s what I was planning on doing

[OBP]

What format is the Area Code?
Alpha, Numeric or AlphaNumeric?

ps OK, I see it is Integer.

[classmaz]

What format is the Area Code?
Alpha, Numeric or AlphaNumeric?

ps OK, I see it is Integer.

I’ve juat looked at the table properties and they have them as text. When I look into it some of the area codes start with a 0.

[OBP]

That is interesting as this
Dim LAF_Code As Integer
sets the LAF_Code to Numeric
What about the Area Code Table?

[OBP]

OK, here is a standalone database that mimics your Table.
On the form select on of the Area Codes and click the test code button and it should send me an email.
To change it to an email address you can access go in to the Area Sales table and put it in.

[classmaz]

Hi just checked it’s text too but I suppose if they turn to interger for both datasets it doesn’t really matter. for example 01 would become 1 and that would still be unique. Just the system dumps the data every day as 01 in a text format

[classmaz]

That dB loads the email but doesn’t automatically send. Also I was hoping that this Code could be incorporated into the original so it would just cycle through the area code table generating the files and emails.

[OBP]

We will get there, this is just for testing.

[classmaz]

We will get there, this is just for testing.

Thanks mate

[OBP]

Sorry for the delay in responding, my Wife has been using the computer.
First of all this line of code

DoCmd.SendObject , , , emailto, , , Subject, Body, True

Is the part that sends the email and the true on the end means it won't send it until it is sent by the user.
Change the true to False and it should then send it automatically.

Next I would like you to Import the Form in my database in to your database and try the button there to see if it can
1. read your Sales Area table and
2. recognise the Area Code.
We may have to change it to Text or change your text to a numeric value for it work with your tables.

[classmaz]

Sorry for the delay in responding, my Wife has been using the computer.
First of all this line of code

DoCmd.SendObject , , , emailto, , , Subject, Body, True

Is the part that sends the email and the true on the end means it won't send it until it is sent by the user.
Change the true to False and it should then send it automatically.

Next I would like you to Import the Form in my database in to your database and try the button there to see if it can
1. read your Sales Area table and
2. recognise the Area Code.
We may have to change it to Text or change your text to a numeric value for it work with your tables.

I will try it later or tomorrow as I am out at the moment. I am going to buy a ms access vba for idiots book as this is just outside my comfort zone. Or do you have another book I should loook at?

[OBP]

It depends on how much you want to spend.
It should give you the basics, although I have never looked at it.
I would suggest having a look in your local library to see if they have anything.