Hi all
I am new to this forum. I have seen some pretty amazing VBA codes/help here. I would like to get involved.
I am a management accountant and I often create workbooks for myself and colleagues to save time and improve efficiency.
To start off, I am sharing the following code which I used to create customer statements from a list of transactions for all customers. At the same time, I hope someone is able to improve it or help with a better way to achieve what I am trying to do here.
Scenario:
I download a big list of customers with individual transactions, our system is unable to email any statement to customer automatically, they are trying to solve the issue but its not going to happen for another good few months.
I want to be able to run a macro that create one statement per customer from the list onto a pre defined template and ultimately, pick the correct email address for each of the customer from another file with list of account ID and email address and then send email with attachment to the correct customer.
Here is a code that works just fine:
I don't have a lot of experience in VBA and Macros, but I can manage tweaking codes to suit my needs which is what I have done above.
Input (starting from cell A1:
Account Account Name Trans. Date Due Date Trans. Type Reference Cust. Reference Terms Days Balance
AB1 ABC Ltd 01/01/2017 31/01/2017 INV INV1234 PO 3456 60 Days 400.00
Output:
Statement layout is exactly the same as above but with logo and what not.
Challenge:
I want to have some flexibility in changing the template layout. i.e. In the output I want the customer account number at the top along with customer name and term days. The rest of the data can appear in rows.
I hope this helps someone and also I hope someone can make some improvement to it.
thanks in advance.
I am new to this forum. I have seen some pretty amazing VBA codes/help here. I would like to get involved.
I am a management accountant and I often create workbooks for myself and colleagues to save time and improve efficiency.
To start off, I am sharing the following code which I used to create customer statements from a list of transactions for all customers. At the same time, I hope someone is able to improve it or help with a better way to achieve what I am trying to do here.
Scenario:
I download a big list of customers with individual transactions, our system is unable to email any statement to customer automatically, they are trying to solve the issue but its not going to happen for another good few months.
I want to be able to run a macro that create one statement per customer from the list onto a pre defined template and ultimately, pick the correct email address for each of the customer from another file with list of account ID and email address and then send email with attachment to the correct customer.
Here is a code that works just fine:
Code:
Sub statementrun()
Dim countrows As Integer
Dim countrows2 As Integer
Dim change As Integer
Dim copynum As Double 'balance column as it contains numbers and I want this to be summed up later
Dim sumup As Double
Dim copystring1 As String
Dim copystring2 As String
Dim copystring5 As String
Dim copystring6 As String
Dim copystring7 As String
Dim copystring8 As String
Dim copystring9 As String
Dim copystring10 As String
Dim emailadd As String
Dim fname As String
Dim fnamecomp As String
Dim acc As String
Dim acc2 As String
Dim acc3 As String
Dim emailterm As Integer
countrows = 0
change = 0
sumup = 0
emailterm = 0
Workbooks.Open ("C:\Statements\Email Source.xlsx") 'open the file containing the email address details
ActiveSheet.Range("A2").Select
Workbooks("C:\Statements\Statement Source file.xlsm").Activate 'open statement source file containing 9 columns, Account, Account Name, Trans Date, Trans. Type, Reference, Cust Reference, Term Days, Balance
Range("A2").Select
acc = ActiveCell.Value
acc2 = ActiveCell.Value
Do While ActiveCell.Value <> ""
acc = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
countrows = countrows + 1
acc2 = ActiveCell.Value
If StrComp(acc, acc2, 1) <> 0 Then ' strings are not the same so the account number changed
countrows2 = countrows
Do While countrows <> 0
ActiveCell.Offset(-1, 0).Select
countrows = countrows - 1
Loop
Workbooks.Open ("C:\Statements\Template.xlsx") '
Workbooks("Email Source.xlsm").Activate
fname = acc
Do While countrows2 <> 0
copystring1 = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
copystring2 = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
copystring5 = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
copystring6 = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
copystring7 = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
copystring8 = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
copystring9 = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
copystring10 = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
copynum = ActiveCell.Value
countrows2 = countrows2 - 1
Workbooks("Manrose Statement Template.xlsx").Activate
ActiveCell.Value = copystring1
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = copystring2
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = copystring5
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = copystring6
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = copystring7
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = copystring8
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = copystring9
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = copystring10
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = copynum
sumup = sumup + copynum
ActiveCell.Offset(1, -8).Select
Workbooks("MML Macro Statement Sending.xlsm").Activate
ActiveCell.Offset(1, -8).Select
Loop
'find email address, return default email address if not found
Workbooks("MML Statement Sending Emails.xlsx").Activate
ActiveSheet.Range("A1").Select
countrows = 0
countrows2 = 0
Do While countrows = 0
copystring1 = ActiveCell.Value
If StrComp(acc, copystring1, 1) <> 0 Then 'strings are not the same
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then
countrows = 1
emailadd = "[EMAIL="xyz@abc.com"]xyz@abc.com[/EMAIL]" 'default email if not found a match, change to appropriate CC
End If
Else
ActiveCell.Offset(0, 2).Select
emailadd = ActiveCell.Value
countrows = 1
End If
Loop
' End of finding the email address
Workbooks("Template.xlsx").Activate
ActiveCell.Offset(1, 8).Select
Selection.Font.Bold = True
ActiveCell.Value = sumup
ActiveCell.Offset(0, -2).Select
ActiveCell.Value = "Statement Balance"
Selection.Font.Bold = True
ActiveWorkbook.SaveAs Filename:="C:[URL="file://\\sl-v-f1\home$\bashir.neyazi\Desktop\Statement"]\[/URL]Statements\Individual Statements" & fname, FileFormat:=xlNormal, ReadOnlyRecommended:=False, CreateBackup:=False
sumup = 0
countrows = 0
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = emailadd
.CC = ""
.BCC = ""
.Subject = "Manrose Statement"
.Body = "Please find your latest statement attached" & vbNewLine & "Kind regards" & vbNewLine & "Credit Control Team"
.Attachments.Add ActiveWorkbook.FullName
.send
End With
ActiveWorkbook.Close
Workbooks("MML Macro Statement Sending.xlsm").Activate
End If
Loop
End SubI don't have a lot of experience in VBA and Macros, but I can manage tweaking codes to suit my needs which is what I have done above.
Input (starting from cell A1:
Account Account Name Trans. Date Due Date Trans. Type Reference Cust. Reference Terms Days Balance
AB1 ABC Ltd 01/01/2017 31/01/2017 INV INV1234 PO 3456 60 Days 400.00
Output:
Statement layout is exactly the same as above but with logo and what not.
Challenge:
I want to have some flexibility in changing the template layout. i.e. In the output I want the customer account number at the top along with customer name and term days. The rest of the data can appear in rows.
I hope this helps someone and also I hope someone can make some improvement to it.
thanks in advance.
