Right Click Send to Recipient Outlook Combobox Draws Blank Values

[whosphil]

How come when I right click on a document and send to recipient, my combobox in my userform is blank? But, when I create a new email and run my userform the combobox has values.
Thanks

link to my code

stackoverflow.com/questions/23790674/outlook-macro-does-run-properly-when-sending-attachment

[westconn1]

please explain, with more deatail
is this a code issue? if so post the code showing where the problem occurs, here


How come when I right click on a document where are you doing this?
in outlook or an explorer window

[whosphil]

I right click on an icon file in explorer window.

Here's my code


Option ExplicitPublic AlbanyLoad As Integer, CanastotaLoad As Integer, ChicopeeLoad As Integer, ColonieLoad As Integer
Public Body As String
Public ShipDate As Date


Private Sub UserForm_Initialize()
With LocationBox
.AddItem "Chicopee"
.AddItem "Albany"
.AddItem "Colonie"
.AddItem "Canastota"
End With

End Sub


Private Sub CommandButton1_Click()Dim oMail As Outlook.MailItem


ShipDate = Format(ShipDateBox.Value, "MM/DD/YY")


Body = "has been uploaded."


Set oMail = ActiveInspector.CurrentItem


Select Case LocationBox.Value



Case "Albany"

With oMail
.To = "Albany Report"
.Subject = "Albany " & ShipDate & " " & WeekdayName(Weekday(ShipDate)) & " Ship Uploaded"
.Body = AlbanyLoadbox.Value & " Albany Loads" & vbLf & CanastotaLoadBox.Value & " Canastota Loads" & vbLf & ChicopeeLoadBox.Value & " Chicopee Loads" & vbLf & ColonieLoadBox.Value & " Colonie Loads" & vbLf & vbLf & "Phil"
End With

Case "Canastota"

With oMail
.To = "Canastota Report"
.Subject = "Canastota " & ShipDate & " " & WeekdayName(Weekday(ShipDate)) & " Ship Uploaded"
.Body = CanastotaLoadBox.Value & " Canastota Loads" & vbLf & vbLf & "Phil"
End With

Case "Chicopee"

With oMail
.To = "Chicopee Report"
.Subject = "Chicopee " & ShipDate & " " & WeekdayName(Weekday(ShipDate)) & " Ship Uploaded"
.Body = ChicopeeLoadBox.Value & " Chicopee Loads" & vbLf & vbLf & "Phil"
End With

Case "Colonie"

With oMail
.To = "Colonie Report"
.Subject = "Colonie " & ShipDate & " " & WeekdayName(Weekday(ShipDate)) & " Ship Uploaded"
.Body = ColonieLoadBox.Value & " Colonie Loads" & vbLf & vbLf & "Phil"
End With

End Select




Unload Me
oMail.Send








End Sub





Public Sub ReportEmails()

UserForm1.Show


End Sub

[westconn1]

How come when I right click on a document and send to recipient, my combobox in my userform is blank?there is nothing in your posted code to populate the combobox in when that is done

you would need to add some event, to trap when a new item is opened, from which you can run code for your userform

[whosphil]

I am a little confused, doesnt this populate my combobox?


Private Sub UserForm_Initialize()
With LocationBox
.AddItem "Chicopee"
.AddItem "Albany"
.AddItem "Colonie"
.AddItem "Canastota"
End With

End Sub

[whosphil]

When I create a new mail
11748

When I use "Send to"
11749

[westconn1]

I am a little confused, doesnt this populate my combobox?
yes, when the userform is initialised
but how are you calling the userform when a new inspector is opened?

[whosphil]

yes, when the userform is initialised
but how are you calling the userform when a new inspector is opened?

I run this module




Public Sub ReportEmails()
UserForm1.Show


End Sub

[westconn1]

I run this moduledo you run this manually or is it called from some event?

do you always load the same values to the combobox?
if so, why not put them in at design time rather than adding them by code?

[whosphil]

do you run this manually or is it called from some event?

do you always load the same values to the combobox?
if so, why not put them in at design time rather than adding them by code?

I run the macro by clicking a shortcut on the ribbon. I believe that is manually. I am fairly new with VBA, so I don't understand the difference between design time vs adding them by code. The values are always the same. I choose one location and more labels pop up depending on which location I choose.


I am not exactly sure what I am doing wrong. Is there something I am missing in the code when I send to>recipient?

[westconn1]

design time means putting the values into the control (or setting any other properties) using the properties box of the combobox when the form is not running
adding by code is known as run time, while the form is loaded, if put in at design time they are always there, no need to add by code as well

[whosphil]

Nothing seems to be working. I cannot add values to the combobox with design time. Were you able to fix this solution by using my code or another way? I hope to fix this soon because it cuts down time sending reports. thanks

[westconn1]

I cannot add values to the combobox with design time.my error, you can not do this with a combobox in outlook


Were you able to fix this solution by using my code or another wayi can not easily test your code, as outlook is not my default email client

[whosphil]

Do you know the reason why I cannot click anything on VBA Editor when I open it after I create an email through send to > recipient ?

The process is this:

1) right click word document
2) send to
3) mail recipient, then email opens with attached file
4) alt + f11
5) vba editor opens

after step 5 I cannot play with the editor, its frozen in place. I have to press alt+f11 again to go back to the email.

I believe there is a hiccup with vba and the send to function. Do you know any ways to fix this?

[westconn1]

it is not uncommon for a VBA editor to lock if the application is in edit mode, i assume this applies to outlook as well and appears to be the case when testing

i can make the combobox populate, but it does not seem to have any value, as nothing else in the userform will work

[whosphil]

So is there no solution to this problem?

[westconn1]

i would not say that, but i certainly can not see one using a userform
there may be another way to achieve the same task, but i would need more information on what you are trying to do

[whosphil]

This is my process:

1) Generate a report through access
2) Right click on the file, send to > mail recipient
3) Auto populate the To, Subject, and Body Field and automatically send


I have to do this 4 times a day to 4 different locations. Each email has a different To, Subject, and Body.

[westconn1]

Each email has a different To, Subject, and Body.where are the strings for these retrieved from?

maybe much better to fully automate the outlook process from access

[whosphil]

where are the strings for these retrieved from?

maybe much better to fully automate the outlook process from access

Lets do an example for one location:

to: albany
subject: albany 6/5 Thu Ship
body: albany 5 loads.

The date, and # of loads will always change.

I agree fully automating the process through access is a better way, but as of now I do not have the power to change the code in access. Which brings be back to what I am trying to do. It is the next best answer to automating through access.

[westconn1]

to: albany
subject: albany 6/5 Thu Ship
body: albany 5 loads.i assume this is coming from your database?

possibly you can use ADO to read data directly from the data base, but user choices (without userform) may be more difficult
or maybe the better alternative is to select the attachment from the userform, using some criteria to make it easier to select, based on folder and file date