Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help witn Syntax 2

Status
Not open for further replies.
tamer64

tamer64

IS-IT--Management
Aug 27, 2007
120
0
0
US
Hello,

I'am hoping someone in this forum can help me. I having some trouble with the syntax below.

I am using MS Access 2010 and I have a form which generates an email through using Outlook.Application. When I outlook generates the email and I click on the Link the website indicates it cannot find the tracking number: TrackingNumber. It appears that the syntax is pulling in the Name field instead of the actual tracking number for the control source from the MS Access form.

Does anyone have idea on what I am missing here. I have tried different variants referencing the name field such as:

=(trackingnumber)
=trackingnumber

strBody = strBody & "<a href=[TrackingNumber]>Track Here" & "</a>"


Any help you can provide is appreciative.
 
Sort by date Sort by votes
Code: 
dim trackingNumber as string
trackingNumber = forms!YourFormName.controlName  
'if called directly from the form module you can replace forms!yourFormName with me
strBody = strBody & "someotherstring" & trackingNumber & "some more string"
 
Upvote 0 Downvote
if you put a variable inside quotes it treats it as a literal

dim myVariable as long
myVariable = 100
debug.print "I have myVariable dollars"
'that prints I have myVariable dollars
debug.print "I have " & myVariable & " dollars"
'that prints I have 100 dollars
 
Upvote 0 Downvote
Thanks for the reply!

I forgot to mention I had already declared the variable. The module is contained on the form itself. I tried placing the variable inside quotes but I receive compile error

Private Sub Command1752_Click()
Dim strBody As String
Dim Contact_Email As String
Dim OutlookApp
Dim Item
Dim Fso As FileSystemObject
Dim TrackingNumber As String

TrackingNumber = Me.TrackingNumber

strBody = strBody & "<html>"
strBody = strBody & "<body>"
strBody = strBody & "<table width='70%'>"
strBody = strBody & "<a href= Here" & "</a>"
strBody = strBody & "<hr />"
' Creating An email
Set OutlookApp = CreateObject("Outlook.Application")
Set Item = OutlookApp.CreateItem(0)
Set Fso = New FileSystemObject

With Item
.Display

End With

'***creates and adds attachment and displays email
With Item
.HTMLBody = "<p><font size='3' face='Times New Roman' color='blue'>" & strBody & "</font></p>"
.To = Contact_Email
.Subject = "TEST" & " " & Date
'.Send
End With
Set Item = Nothing
Set OutlookApp = Nothing
Exit Sub
' Clear and exit email
Set OutlookApp = Nothing
Set Item = Nothing
MsgBox Err.Description & " See Status Bar"
DoCmd.Close
 
Upvote 0 Downvote
I am a little confused on what the final string should look like. Can you give an example of a tracking number and the complete final string?

Also you can debug this yourself

Code: 
strBody = strBody & "<html>"
strBody = strBody & "<body>"
strBody = strBody & "<table width='70%'>"
strBody = strBody & "<a href=[URL unfurl="true"]https://tools.usps.com/go/TrackConfirmAction!input...[/URL] Here" & "</a>"
strBody = strBody & "<hr />

debug.print strbody
 
Upvote 0 Downvote
Once I click on the button on the main form, it runs the code above and creates and displays the outlook email, it places the Link on the new email. When I click on the Link it does open the browser to USPS website but it is not displaying the tracking number on the website.

It should look like this on the site at the very top of the webpage. It then displays all traking information.

Tracking Number: 3435454990345903092093


But instead it look like this. It appears it is grabing and inserting the name field of the control source, instead the actual data.

Tracking Number: TRACKINGNUMBER

Hope this all makes sense..
 
Upvote 0 Downvote
It is still unclear to me what this final string should look . I do not get the ellipses and equal signs. But lets assume I want a string that looks like this

"<a href= = 3435454990345903092093 >Track Here </a>"

To get the tracking number into the string you need to concatenate it to the strings.
"string " & variable & " string "
open quote close quote ampersand variable ampersand open quote close quote

strBody = ""<a href= = " & trackingNumber & " >Track Here </a>"

The variable has to be outside the quotes and concatenated using the ampersands
 
Upvote 0 Downvote
Or the same as MajP's, but this way:
[tt]
strBody = strBody & "<a href= "
strBody = strBody & TrackingNumber
strBody = strBody & "</a>"
[/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
Got it to work! thank you both for all your help.
 
Upvote 0 Downvote
Could you share/post your solution so others who will look at this thread would know what works?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
Or even better

Code: 
strBody = strBody & "<a href=[URL unfurl="true"]https://tools.usps.com/go/TrackConfirmAction!input...=[/URL] "
debug.print strBody
 'Does it look good?
strBody = strBody & TrackingNumber
debug.print strBody
 'Does it look good?
strBody = strBody & "</a>"
debug.print strBody
 'Does it look good?

Need to be able to debug your complex and long code strings.
 
Upvote 0 Downvote
@tamer64

Over the past 7 years you have posted 42 Threads and have received many very good tips.

You apparently got some good help from these two members.

How about doing a better job to...
PostingGuidelines said:
Thank Other Members For Help Received [highlight #FCE94F]& Click the "Thank (member) For This Valuable Post!" Link[/highlight]
Click to expand...
 
Upvote 0 Downvote
I dont why "..." kept showing up in the post. But this is what worked.

strBody = strBody & "<a href= & TrackingNumber & ">TrackMe </a>"

I think before I had some spaces in where they shouldn't have been.

Again, Thank you.

And for SKIPVOUGHT...I do I appreciate there assistance and if you are reffering to the thread not getting posted, that's my fault it has been a busy day.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
127
combo
combo
SLW89
  • Locked
  • Question
VBA newbie needs help
Replies
4
Views
144
Andrzejek
Andrzejek
feipezi
  • Locked
  • Question
Hoe to fix run time error 2147217900 (80040e14) syntax error in Order By clause 1
Replies
6
Views
578
Andrzejek
Andrzejek
arvarr
  • Locked
  • Question
Excel Help - Worksheets by UserID 3
2
Replies
39
Views
260
arvarr
arvarr
renigar
  • Locked
  • Question
Excel VBA Lookup syntax error fix or alternative
Replies
11
Views
124
renigar
renigar

Part and Inventory Search

Sponsor

Back
Top