Macro to insert sequential reference number into subject of email...

[lamensterms]

Hey guys,

Brand new to the forum, so Hi!

Just wondering if I could please have a bit of help coming up with a macro to insert a subject into an email. I am a draftsman and we number our jobs with 4 digit job numbers. I would like macro to enter a job number, plus a reference/tracking number into the email subject.

The format would be something like "XXXX (GC-001) -". Where XXXX represents the job number, and GC-001 represents a sequential tracking number to increase for each email sent (GC-001, GC-002, GC-003, etc).

The tricky part may be... I would like the latest tracking number to be read from a central database (stored on our local network), so each user can ensure that each tracking number is unique for that particular job.

I was thinking the job number could be requested by the macro... So when the macro is executed, a text input box would open asking the user for the job number, then the macro would read the last tracking number from that job's database (or text file).

I have no experience writing macro code, so any help would be greatly appreciated.

I hope this is not asking too much.

Thanks a lot for taking the time to read.

Ps, we use Office/Outlook 2003.

[westconn1]

I was thinking the job number could be requested by the macro... So when the macro is executed, a text input box would open asking the user for the job number, then the macro would read the last tracking number from that job's database (or text file).

the tracking number should only be update on sending, to prevent multiple users getting the same number or numbers getting omitted if an email is cancelled for any reason, the database would also need to be updated immediately after use of current number

without knowing the type of database, hard to make working code, but presumably you can access the database using ADO, read the number to variable, update the number, then format the variable into message subject

how is the number stored in the database?
would the number be appended or prepended to any other subject string?

[lamensterms]

Hi westconn1,

Thanks so much for the reply.

That is an excellent point regarding when the tracking number is assigned. I had not considered the possibility that the email may not be sent, thus 'spending' a tracking number on an unsent email. Would it be possible for the routine to assign the tracking number during the send process? For example... The user could hit a button which would insert a subject prefix such as "XXXX (GC-???) - ", and then manually enter a description into subject line. Then when the user hits a "send and track" button, the macro would read the tracking number from a database, and replace "???" in the subject line with the appropriate tracking number? After which that 'used' tracking number would be written back the database, and the next time the macro is used... The next number (previous number + 1) would be used.

As far as what type of database... I guess I used the term 'database' pretty loosely. The 'number register' could be anything it needs to be. I originally thought maybe a TXT file would suffice, but I would be happy for someone to advise me on what would be best.

I really know nothing about how to achieve the result, but I am willing to learn and am open to any and all suggestions.

Thanks again for taking the time to reply.

[westconn1]

Would it be possible for the routine to assign the tracking number during the send process?

yes code in the itemsend event

would insert a subject prefix such as "XXXX (GC-???) - ", and then manually enter a description into subject

i would let them put the subject as normal, then auto add the prefix at sending

I originally thought maybe a TXT file would suffice,

that could work, but probably a more robust solution should be used, even if it is overkill

[lamensterms]

Hi westconn1,

Thanks for the reply.

Ok, So I have the following code:

Sub GetJobNumber()

Dim strName As String

 Dim objMsg As Outlook.MailItem
 Set objMsg = Outlook.ActiveInspector.CurrentItem

    strName = InputBox(Prompt:="Please Enter Job Number:", _
          Title:="ENTER JOB NUMBER", Default:="Job Number Here...")

          

        If strName = "Job Number Here..." Or _
           strName = vbNullString Then

           Exit Sub

        Else

          Select Case strName

            Case "1068 (GC-###) - "


 objMsg.Subject = "1068"

            Case "1071"


 objMsg.Subject = "1071 (GC-###) - "

            Case "1356"


 objMsg.Subject = "1356 (GC-###) - "

            Case Else

 NoJobNumber

          End Select

        End If

  

End Sub


Sub NoJobNumber()

 Dim objMsg As Outlook.MailItem
 Set objMsg = Outlook.ActiveInspector.CurrentItem

     iReply = MsgBox(Prompt:="Job Number not registered for tracking, continue with 'misc' job number?", _
            Buttons:=vbOKCancel, Title:="Job Number Not Found")
            
                If iReply = vbOK Then


 objMsg.Subject = "XXXX (GC-###) - "

                If iReply = vbCancel Then Exit Sub
                End If
                
                
End Sub

Which seems to work OK so far. It will prompt the user for a job number, and then insert a subject prefix according to the job number entered. It will also alert the user if the job number has not yet been considered/allowed for in the 'system'.

I've done a bit of googling about how to read/write a text file as the tracking number database, but haven't been able to find much that I am able to understand. Is there a function or article you can suggest to get me started? I'm basically looking for a way to read, write and +1 a 3 digit number/counter to a .TXT or Excel file, then substitute that new number into the subject line of the email as it is sending.

Thanks a lot for any help.

[westconn1]

try like

fn = "c:\temp\qwerty.txt"
f = FreeFile
Open fn For Input As f
    num = Val(Input(LOF(f), #f))
Close f
Open fn For Output As f
    Print #f, num + 1
    Close f

change file path\name to suit, if you want leading zeros on your number, format to suit, num variable content can be inserted to any string
in this case total file content was assumed to be one number (66)

[lamensterms]

Oh awesome, thanks a lot for that westconn1, I've got a bit of tweaking and customising to do. But I will certainly post back with a more developed draft of the code.

Thanks again for helping me get started.

[lamensterms]

Hi again everyone,

So I have the below form with code (which for some reason I cannot post).

Which works ok. The code is still quite rough, but I do have a lot to learn.

One of the biggest issues I am having is - during the "CommandButton1_Click" sub, the find/replace "###" with the 'tracking number' works fine... but it will fail to include the text selected from combobox3. If I type manually into combobox3, the text is included when when I hit CommandButton1, but the text is removed if it is chosen from the combobox3 pulldown. If that makes sense.

Can any please help me figure out where I might be going wrong?

[lamensterms]

1. If I select text from the pulldown list in ComboBox3, the text is not included in the final subject string. If I manually input text into ComboBox3, the text is included in the final subject string.

2. When selecting OptionButton2 (to input "TR" into the subject string... TR is initially shown in the subject bar, but is replaced by "GC" upon hitting CommandButton1.

Both these errors seem like they might be related to a lingering variable which is being used, maybe not correctly defined/cleared (just a guess from my AutoCAD LISP experience.

I'm afraid I am not able to attach the code, which without being able to do so... help will be pretty hard to find. I'll keep trying.

[lamensterms]

Private Sub ComboBox1_Change()

End Sub

Private Sub ComboBox3_Change()


 Dim objMsg As Outlook.MailItem
 Set objMsg = Outlook.ActiveInspector.CurrentItem
 
 Dim OSubject As String
  
 objMsg.Subject = Left(objMsg.Subject, 16) & ComboBox3.Text



End Sub

Private Sub CommandButton1_Click()

 Dim objMsg As Outlook.MailItem
 Set objMsg = Outlook.ActiveInspector.CurrentItem
 
  Dim OSubject As String
  

 
If ComboBox1.Text = "1068 - MUGCF" And OptionButton1 = True Then

 fn = "....1068 - GC.txt"
f = FreeFile
Open fn For Input As f
num = Val(Input(LOF(f), #f))
Close f
Open fn For Output As f
Print #f, num + 1
Close f

'OSubject = objMsg.Subject
'objMsg.Subject = Replace(objMsg.Subject, "(TR-", "(GC-")
objMsg.Subject = Replace(objMsg.Subject, "###", Format(num, "000"))

End If


If ComboBox1.Text = "1068 - MUGCF" And OptionButton2 = True Then

 fn = "....1068 - TR.txt"
f = FreeFile
Open fn For Input As f
num = Val(Input(LOF(f), #f))
Close f
Open fn For Output As f
Print #f, num + 1
Close f

'OSubject = objMsg.Subject
'objMsg.Subject = Replace(objMsg.Subject, "(TR-", "(TR-")
objMsg.Subject = Replace(objMsg.Subject, "###", Format(num, "000"))
 
End If


UserForm1.Hide

End Sub

Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub

Private Sub OptionButton1_Click()

 Dim objMsg As Outlook.MailItem
 Set objMsg = Outlook.ActiveInspector.CurrentItem
 
 Dim OSubject As String
 
 OSubject = objMsg.Subject
 
 objMsg.Subject = Replace(OSubject, "TR", "GC")

End Sub

Private Sub OptionButton2_Click()

 Dim objMsg As Outlook.MailItem
 Set objMsg = Outlook.ActiveInspector.CurrentItem
 
 Dim OSubject As String
 
 OSubject = objMsg.Subject
 
 objMsg.Subject = Replace(OSubject, "GC", "TR")

End Sub

Private Sub UserForm_Activate()

 ComboBox1.AddItem "1068 - MUGCF"
 ComboBox1.AddItem "1071 - Flinders St"
 ComboBox1.AddItem "1356 - Mt Piper"
 
 ComboBox3.AddItem "RFI Register"
 ComboBox3.AddItem "IFC Package"
 
 End Sub
 
 
Private Sub Combobox1_Click()
   
'Dim strName As String

 Dim objMsg As Outlook.MailItem
 Set objMsg = Outlook.ActiveInspector.CurrentItem
  
             Select Case ComboBox1.Text

            Case "1068 - MUGCF"

 objMsg.Subject = "1068 (GC-###) - "
  objMsg.CC = "..."
 OptionButton1.Value = True
 ComboBox3.Value = ""
 

            Case "1071 - Flinders St"

 objMsg.Subject = "1071 (GC-###) - "
  objMsg.CC = "..."
 OptionButton1.Value = True
 ComboBox3.Value = ""

            Case "1356 - Mt Piper"

 objMsg.Subject = "1356 (GC-###) - "
  objMsg.CC = "..."
 OptionButton1.Value = True
 ComboBox3.Value = ""
 

            Case Else

 NoJobNumber

          End Select
   
End Sub

[lamensterms]

Ok, so I tested this code on an installation of Outlook 2013, and issue #2 from post #9 is non-existant on the newer version. Issue #1 still remains.

Does anyone know how I may be able to resolve the 'find/replace' problem I am having with Outlook 2003? I have tried a repair install without any luck.

Looking forward to hearing back.

[Charlize]

Maybe change the .Text part to .Value when the value of combobox3 changes.

Sorry, the code tags don't seem to work.

Private Sub ComboBox3_Change()
Dim objMsg As Outlook.MailItem
Dim OSubject As String
Set objMsg = Outlook.ActiveInspector.CurrentItem
objMsg.Subject = Left(objMsg.Subject, 16) & ComboBox3.Value
End Sub

[lamensterms]

Hi Charlize,

Thanks a lot for the reply. Unfortunately, changing the 'text' to 'value' did not seem to help. I am just in the process of trying to understand the actions assigned to each 'private sub' (click / change / afterupdate / etc). Can you see any need to change any of these within my code above?

Also, do you have any experience with unreliable VBA with Office 2003? This macro seems to perform a lot better on Office 2013.

[westconn1]

Also, do you have any experience with unreliable VBA with Office 2003? This macro seems to perform a lot better on Office 2013.

as i am still using an earlier version of outlook, it is hard to be certain what version incompatibilties there may be, but replace is a basic vba function and should work correctly in all versions
i have just retested your code above and it works for me, to replace ### with a numeric string