I receive many requests for help in adding numbers and codes to Outlook subject lines. Some users want to sequential numbers, such as you'd use with invoices, others want to use random numbers or random characters for tracking purposes.
These code samples are basic VB and will work in many situations, in any Office application, or in VB (if anyone still uses VB outside of Office applications).
My examples display the number or random characters in a message box, but you can use it anywhere you'd use a string or variable.
With sequential numbering you have two options: save the number in the registry or save the number in a text file. Saving the number in a text file allows you to use it on multiple machines, or shared with other users. Storing it in the registry means you don't have a text file laying around.
This code sample adds the value to the registry, at HKEY_CURRENT_USER\Software\VB and VBA Program Settings\Outlook\Invoices (You can change the sAppName, sSection, and sKey names in the code, if desired.)
Sub AddInvoiceNumber() Dim sAppName As String Dim sSection As String Dim sKey As String Dim lRegValue As Long Dim lFormValue As Long Dim iDefault As Integer sAppName = "Outlook" sSection = "Invoices" sKey = "Current Invoice Number" ' The default starting number. iDefault = 101 ' adjust as needed ' Get stored registry value, if any. lRegValue = GetSetting(sAppName, sSection, sKey, iDefault) ' If the result is 0, set to default value. If lRegValue = 0 Then lRegValue = iDefault ' Increment and update invoice number. SaveSetting sAppName, sSection, sKey, lRegValue + 1 ' do whatever with the random number MsgBox CStr(lRegValue) End Sub
This example generates a random number between 1 and 10,000. Change the High and Low numbers to restrict the random number to a specific number range.
Sub UseRandomNumber() intHigh = 10000 intLow = 1 Randomize intNumber = Int((intHigh - intLow + 1) * Rnd + intLow) ' do whatever with the random number MsgBox intNumber End Sub
Random alphanumeric characters
This code sample generates a 10 character alphanumeric code.
To create longer (or shorter) codes, change the value in GetRandom(10).
This function generates codes using upper and lowercase letters and numbers. Non-alphanumeric characters are replaced with random uppercase letters or numbers. (See the ASCII table for the character codes.)
Remove the two If iRand > lines to include those characters.
Sub UseRandomCharacters() ' change 10 to another number for longer key ' do whatever with the random number MsgBox GetRandom(10) End Sub Function GetRandom(Count) Randomize For i = 1 To Count iRand = Int((122 - 48 + 1) * Rnd + 48) 'remove non-alphanumeric characters, replace with uppercase or numbers If iRand > 90 And iRand < 97 Then iRand = Int((90 - 65 + 1) * Rnd + 65) If iRand > 57 And iRand < 65 Then iRand = Int((57 - 48 + 1) * Rnd + 48) GetRandom = GetRandom & Chr(iRand) Next End Function
Create a serial number (VBOffice.net)
Macro to Increment Invoice Number to New Form Document (MSKB) This macro works in all versions of Office. It's the basis for the AddInvoiceNumber code sample above.