Message template / custom forms and VBA Help needed - inserting info into table

[GingaDave]

Hi, I'm stuck and i am needing help.

i have created a message template to be used for admin, but they want their lives made easier. Now the boss has asked me to sort it out.
Basically, the issue is this. Below is an image of the template. It was created in a table. at the moment the admin are filling in all fields themselves. Now they want some fields filled in automatically. The fields they want auto-filled are :


Date created: - Populates the date that the message was created.

ATTACHED FOR REFERENCE - This is a list of all of the attachments that have been attached to the email.

FILE PATH STRUCTURE ON SERVER - This is a list of the location where the attachments were inserted from. ( eg c:/jobs/5467/pdfs/drawing0042.pdf )

I have found some code from other places that help me to some point with the 1st field and 2nd field but not the 3rd field.

1st field - the code works (from Diana - thanks) but i need it to be in the correct cell.

Sub StampDate()
   Item.DTS1 = Now()
End Sub


Sub InsertDate_Click() 
  Call StampDate() 
End Sub

2nd field - this also needs to be in the correct cell, and also i cant seam to create a custom form button to run this script (would be nice if it was one button for all )

Sub Names()

Dim Atmt As Attachment
Dim Mensaje As Outlook.MailItem
Dim Adjuntos As String
Dim Body As String
Dim i As Integer


Set Mensaje = Application.ActiveInspector.CurrentItem
Mensaje.BodyFormat = olFormatHTML

Body = Mensaje.HTMLBody

i = 0
Adjuntos = ""

For Each Atmt In Mensaje.Attachments
    'If Atmt.Size > 5 Then
    Adjuntos = Adjuntos & "** Attached file: <u> " & Atmt.FileName & " </u> <br>"
    i = i + 1
    'End If
Next Atmt

Adjuntos = "<u> <b> Total number of attached files: " & i & "</u></b> <br>" & Adjuntos

Mensaje.HTMLBody = Left(Body, InStr(Body, "</body>") - 1) & Adjuntos & Right(Body, Len(Body) - InStr(Body, "</body>") + 4) & Format(Now)

Set Mensaje = Nothing

3rd field - Total loss - i need the code and button (again if was same button would be fab)


Cheers

GingaDave

 

[GingaDave]

sorry - mistype in stampdate code - should read

Sub StampDate()
   Item.body = Now()
End Sub


Sub InsertDate_Click()
  Call StampDate()
End Sub

 

[Michael Bauer]

Access user properties this way

item.userproperties("name").value = "abc"

As to the third issue, I don't know a way to get that info from Outlook. I think you'd need to write the part to add the attachment to the email yourself. If you drag an item to the email, the file path is available in the clip board.

 

[Diane Poremsky]

The date stamp code is vbscript and added to the form. To the best of my knowledge, you can't work with cells using vbscript. You can do it in word using VBA - so as long as you are using VBA and not a script in the custom form, you might be able to get it into the cell. (Command buttons on forms use VBscript... )

This is the code i use in Word to read the cell in the first row, 3rd column in the first table. In a quickie test the other day, I tried converting it to Outlook but didn't get the tables referenced right.

Dim myTable As Word.Table
Set myTable = ActiveDocument.Tables(1)
txtNotes = myTable.Cell(1, 3)

Making your other macro work with a command button starts with changing the name to match the button name - like the Sub InsertDate_Click() is named. I believe this only works with vbscript (Michael will correct me, if I'm wrong). If so, you would need to convert the macro to script - there are subtle differences between vbscript and VBA and not everything is supported. I haven't looked closely at your code to know if it will work in vbscript. It is possible to call vba from a script, but it's not something microsoft recommends (plus the form is no longer self-contained and not as easily shared).