Settle Credit Notes By VBA
Aug 15, 2014
I need a macro that searches through column F(amount). If it finds a negative amount(a credit note), it should search through column A(client ID), look for the same client and deduct the amount from the oldest invoice of this client. Then it should delete the whole line of the credit note.
If it doesn't find the same client, it should not adjust anything.
If the negative amount is a cancellation(all records with the invoice number 9999999), it should not adjust anything.
If the amount of the oldest invoice is smaller than the credit note, it should deduct down to 0 and deduct the rest of the credit note from the second oldest invoice.
I have attached a sample file : makro credit note.xlsx‎
View 4 Replies
ADVERTISEMENT
Jun 19, 2007
I have a database which holds a field for people's credit card numbers. When I try to import them into excel it removes the last number and replaces it with a 0. So if I have credit card number 123456789123456 it will show as 1.23456E+15. Then when i format it as a number is shows as 123456789123450.
View 11 Replies
View Related
Dec 19, 2007
I have been trying to write a macro but its getting very complicated and im just getting confused. I need to look through a long list of data and see if there is a comparable figure. e.g if there is £350 at branch A for supplier X, i need to know if there is also a credit of £350 at branch A for the same supplier..
The problem is that the comparable figure could be above or below the original figure. Then i will delete both these lines from the list. I have tried to do this but have got cofused by so many if and else formulas.
View 5 Replies
View Related
Apr 6, 2012
Why doesn't a three column formula work?
Column C is Credit, D is Debit, E is Balance
I put the following formula in the Balance column
=E21+C22-D22
where the first term refers to cell immediately above. In other words, this formula takes the prior balance, adds a credit if any, subtracts a debit, if any, and gives a new balance.
The formula worked on the cell I put it in. It will not work when I copy and pasted it into the cell below the first one. Error I get is circular reference.
View 2 Replies
View Related
Mar 19, 2008
I have a userform capturing Credit Card Numbers for Visa, Master, Amex and Diners as part of a payment tracking system and would like to check that the entered Card Number is valid before processing the payment through a third-party website to minimize to number of error / invalid card messages.
I suspect there is some kind of checksum built into the cardnr. Any tips on how to check for the validity would be appreciated, including links to relevant sites.
this question is related to an earlier question of mine: [url]
View 9 Replies
View Related
Nov 20, 2013
I need to extract the credit card expiration date from a cell. For example
1111 1111 1111 1111 1/17 code 1111
I was able to get the /17 using =MID(C77,FIND("/",C77),4)
However, I can't get the "1" before the /.
View 5 Replies
View Related
Mar 5, 2010
I have 2 reports starting col A to W. I want a macro code for the below.
1st Report
1. 1st sort the entire report with col O leaving the headings.
2. 2nd delete the rows if where value of O is "Credit"
3. After the above step sort the entire report again with col H
2nd Report i hvae is also from Col A to AB. I want a macro code for below.
1. 1st sort the entire report with col O leaving the headings.
2. 2nd delete the rows if where value of O is "Credit"
3. 3rd delete the rows if where value of AB os "DEL"
4. After the above step sort the entire report again with col H
View 9 Replies
View Related
Jun 25, 2007
is it possible to fix remainder for notes in excel sheet.
View 13 Replies
View Related
Jul 23, 2009
THREAD SOLVED...and very well too
I need to find all 3, 4 and 5 letter permutations based on 5-8 musical notes as input. FYI the permutations will be chords. i.e. C, D, E, F, Ab, Bb, B as an input musical scale input would yield
All three note combos :
C,D,E
C,D,F
C, D, Ab
....
all 4 note combos
C,D,E,F
C,D,E,Ab
all 5 note combos
C,D,E,F,Ab
C,D,E,F,Bb
...
I'd like to structure the sheet as having 12 columns (1 for each note within an octave) with the combo placed under the right column i.e. C goes in 1, Eb in 4.
View 5 Replies
View Related
Jun 2, 2014
I have an excel file that will be downloaded by multiple users and saved locally. I was wondering if there was a way to build code into the file so that everytime it is saved an email would be created in lotus notes from the user to me with a copy of the file attached. Is this possible?
View 2 Replies
View Related
Oct 30, 2006
I have developed many macros that generate automatic emails from excel using Outlook, with the excellent help I've found here. Now due to recent buyouts, we have changed our email from Outlook to Notes. I'm looking for the simplest method of changing my macros so they will work with Notes. Below is the typical code I'm currently using. Is there an easy way to change it for Notes?
Public Sub SendEmail()
Dim OutApp As Object
Dim OutMail As Object
Dim Dist As String
Dim supplierinfo As Range
Dist = WorksheetFunction.VLookup(Supplier, Workbooks("ScarForm.xls").Sheets("Supplier Information").Range("a9:z1000"), 3, 0)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.to = Dist
.CC = "emailnames"
.BCC = ""
.Subject = "email subject"
.Body = "Text of email"
Windows(Fname & ".xls").Activate
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
View 9 Replies
View Related
Nov 17, 2006
I have problems with passwords in excel 97. The perfect Script below wheel in excel 2000 and 2003, but presents problems in excel 97.
Function EnviaBancoALTEND(Para As String, Doc As NotesDocument, DB As NotesDatabase, Password As String)
Dim ws As New NotesUIWorkspace
Dim s As New NotesSession
Dim Doc_Mail As NotesDocument
Dim RTItem As NotesRichTextItem
Dim object As NotesEmbeddedObject
Dim xlApp As Variant
Dim xlsheet As Variant
'cria o excel
Set xlApp = CreateObject("Excel.application")
xlApp.Visible = False
xlApp.Workbooks.Add
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
XlApp.Windows(1).DisplayGridlines=False
View 9 Replies
View Related
Oct 2, 2008
I am developing a VBA program that will take rows of data from a worksheet and create Lotus Notes Calendar entries.
My code works fine until it wants to add a calendar entry dated in the past... at this time I get a pop up message on Notes saying "This entry is being created in the past.
Create anyway?" and an option to click yes or no..
how to either suppress the pop up or programatically supply a "YES".
View 9 Replies
View Related
May 15, 2009
I'm in the middle of creating an auto-mailer via Excel VBA (and got most of it working) but I've run across a small(ish) snag.
To save me about 3 hours work, I need to get the subject of the stationery, the "To" list, the "CC" list and the "BCC" list for every stationery (currently around 150).
Is there any way of getting VBA to read all the stationeries on our inbox (it's shared) and giving me the info I need?
View 9 Replies
View Related
Aug 4, 2009
I'was given a piece of code to read through stationeries in Lotues Notes so I could quickly retrieve all the Sendto, CC and BCC information.
I'm now trying to modify that so I can check two specific folders in a mailbox and retrieve all the dates that e-mails have been received on.
Code is here:-
Sub new_reader()
Dim Maildb As Object, view As Object, Session As Object, entry As Object, entries As Object
Sheets("Sheet4").Select
counter = 2
Set Session = CreateObject("Notes.NotesSession")
Set Maildb = Session.GETDATABASE("servername", "boxname")
If Maildb.IsOpen = False Then
Maildb.OPENMAIL
End If
View 9 Replies
View Related
Dec 17, 2009
Can we send mail through lotus notes in excel.
Request you guide me with this as to how to do it and what the code is.
I need to send a simple mail in body and as an attachment
View 9 Replies
View Related
Feb 6, 2004
I received an excel spreadsheet through lotus notes e-mail. I opened it from the e-mail and worked several hours on it, sporadically hitting "save" along the way. However, I do not recall having ever done "Save As" and specifying a location to save it in. I later closed excel. Now I cannot locate the spreadsheet (I have searched). Do you know where it might be located or how I might recover it? When you open spreadsheets directly from e-mails and just hit save (not save As),
View 9 Replies
View Related
Aug 18, 2014
I am looking to build a macro that can automatically email a recipient upon their name being selected from a drop down list. It designed to be for a warehouse, so when new packages come in we can scan them in and have excel send an email out once our receiving team determines who it is for. With their being multiple recipients, I would like the code to have an IF type function that choosing the correct recipient. the Column titles "Recipient/Dept" is where the dropdown list is located.
It would be ideal for the code to just utilize an open Lotus Notes Session and use that to send the email.
Attached is the document with what I have thus far : Warehouse barcode test.xlsm
View 14 Replies
View Related
Nov 8, 2007
I have a macro that sends a custom email through lotus notes. It works, but will only generate 1 email instead of looping through my list. If I have "next r" at end I get email of 1st record. If I put "next r" before "On Error GoTo Audi"
I get last recorded emailed but not 1st 2. I don't know how to get it to send all. It seems the for next loop is jacked up.
For you to see macro work you need to have lotus notes and put email address in column B.
test file is attached.
View 10 Replies
View Related
Sep 17, 2008
I have the following code from Ron De Bruin's website (http://www.rondebruin.nl/mail/folder3/message.htm) for emailing a small message from a range.
View 14 Replies
View Related
Dec 14, 2011
I need to be able to update columns A:H from a file, but then retain the user input information in columns I:L
Import the file into an array, lookup and append I:L data then overwrite?
I need to use this approach in several sheets - import an XLS data file and build a summary, retaining notes used previously
[URL]
View 1 Replies
View Related
Feb 9, 2012
I have a networked spreadsheet that users access, process data, and then e-mail the results to a common recipient.
Until recently we have all been on Lotus Notes - and my e-mail VBA worked OK.
However, some users are now using Outlook (the new Corporate standard) while some remain on Lotus Notes. The Lotus Notes users now say the e-mail part of the process no longer works for them.
Can VBA detect whether users are on Outlook or on Lotus Notes?
Should I give them a prompt - "Which e-mail do you use? Enter L for Lotus Notes or O for Outlook" - and if they answer L then use specific Lotus Notes VBA for the e-mail process or if they answer O use Outlook-specific VBA?
View 3 Replies
View Related
Feb 18, 2013
I have found the code that I need to send one spreadsheet or the entire workbook via lotus notes through VBA, but I cannot seem to find the way to email 2 specific spreadsheets anywhere..
I have tried both of these, but I get an error when running:
Code:
With Worksheets("Diario") And Worksheets("Periodo")
.Copy
stFileName = Worksheets("BD").Range("A1").Value & "- NDG " & Worksheets("Code").Range("K22").Value
Debug.Print stFileName
End With
Code:
With Sheets(Array("Diario", "Periodo"))
.Select
Selection.Copy
stFileName = Worksheets("BD").Range("A1").Value & " - NDG " & Worksheets("Code").Range("K19").Value
Debug.Print stFileName
End With
I would like the 2 sheets (Diario & Periodo) to be copied both to the same temporary workbook...
View 1 Replies
View Related
Jul 17, 2013
Any ways to automatically send emails using Lotus Notes.
On Lotus Notes, I automatically have my signature to append to an email, however when I am running Johns script at the bottom it is replaced with the file path of my signature (i.e. C:Program Fileslotus
otesdatasig.htm), and when I comment out the part where strSignature is mentioned there is no signature.
My code is below:
Sub mySub()
Dim x As Integer
Dim UserName As String
Dim MailDbName As String
Dim Recipient As Variant
Dim Maildb As Object
Dim MailDoc As Object
[Code] ...........
View 1 Replies
View Related
Jun 12, 2014
I have a spreadsheet with note in it. How can I do a search to see what words or phrases keep coming up in the note. with out doing a sort.
View 4 Replies
View Related
May 12, 2006
I managed to create the attached macro with help from stuff I've found in the internet. With this code, I am able to save the file and then send an email cia Lotus Notes. I need two things:
(1) The code to attach the file saved into the email that it sends
(2) The code so that the file is protected so that changes are not made by the recipient.
HERE IS THE
ActiveSheet.Copy
ActiveWorkbook.SaveAs filename:="V:TFMCO8 ReportingHistory" & [c22].Value & " " & Format$([c20], "- (YYYY-MM-DD)") & ".xls"
ActiveWorkbook.Close False
Dim Maildb As Object, UserName As String, MailDbName As String
Dim MailDoc As Object, Session As Object
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, _
(Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen Then
Else: Maildb.OpenMail...........
View 9 Replies
View Related
Dec 28, 2007
I have Excel 2003 and Lotus Notes Version 6 and I need to send out the active workbook from Excel. I have searched through the existing threads, but all the code that I run results in errors -
View 9 Replies
View Related
May 14, 2009
I'm trying to modify.
The code is this:
Dim finder As Object
Private Sub ComboBox1_Change()
Set finder = Sheets("Sheet1").Columns("A:A").Find(what:=ComboBox1.Text, lookat:=xlWhole)
Main_recipients.Value = finder.Offset(0, 1)
End Sub
Private Sub Send_email_Click()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
Now, everything works fine until it gets to this line:-
MailDoc.SEND 0, Recipients
when I get an error message that states "Unable to send mail, no match found in Name & Address Book(s)"
In Lotus, I created a list of recipients (including any additions after the name) and copied that into the cell that feeds the text-box "Main_recipients".
The odd thing is, if I just email myself without any additions after my name it's fine. It's when I try to e-mail multiple people (with and without additions) that it falls over.
View 9 Replies
View Related
Feb 13, 2004
Finally I have the code all okay for Sending Reports, and other extra comments etc...
And now...
Is there code available for picking up images ( Screen snaps) and inserting into an email in Notes after the Body of the Message?
Either a reference to a link or some code would be greatly appreciated, cos I cannot find any info at this point in the archives ( Presuming I have searched using proper parameters ).
View 9 Replies
View Related
Oct 22, 2013
I am trying to open an .xls excel file within a non-email lotus notes database with vba code in excel.
View 3 Replies
View Related
